Query designer in management studio sql server 2005

I have been using Management Studio for SQL-server 2005 for a while now. However, I have never really liked it because of the really strange user interface. Compared to the simple Enterprise manager I first thought that Management studio was a nightmare. But I guess it takes some time to get used to…

I have searched for a simple and quick way to start the query designer. I only want to do a couple of querys and exit. In enterprise manager you only had to browse for a table, right click it and choose open table -> query. The same procedure in management studio takes a lot more clicks and is not worth the effort.

Today I found this post which describes how to access the query designer in SQL-server 2005 Management studio in a quick way. I also found out that it had a lot more features than the earlier version. That’s really good.

How to access the query designer the fastest way:

1. Start Management studio and connect to server.
2. Browse for the database and select it.
3. Press Ctrl+N for a new query window.
4. Press Ctrl+Shift+Q to get the query designer.
5. Rock on.

This is a great feature that is added to what previously was named ”Query analyzer”. The results of these queries are however not editable like they were in Enterprise managers query designer. I guess I have to through the ”open table” (and get all rows) procedure for that functionality. That is really strange, why couldn’t thay have left the ”open table -> query” option?

Shrinking ALL Truncate Log Files

The transaction logs on your SQL-server can fill up a lot of disk space. In my case, I monthly needed to truncate all the log files from the databases that I get from our production environment. These log files are really huge. I need to truncate them because I do not need the logs on my development machine. I made this little T-SQL script to make my (and your) life easier.

declare @db varchar(300)
declare @logname as varchar(300)
declare @sql nvarchar(1000)

declare cDB cursor for
SELECT name from master..sysdatabases sdb
WHERE sdb.name NOT IN('master','model','pubs','msdb')
ORDER BY name

open cDB
fetch cDB into @db

while (@@fetch_status = 0)
begin

--print @db
SET @sql='SELECT @logname=name FROM '+@db+'.dbo.Sysfiles WHERE fileid=2'
--print @sql
EXEC sp_executesql @sql,N'@logname varchar(300) out,@db varchar(300)',@logname out,@db
--print @logname

SET @sql='Use '+@db+';'
SET @sql=@sql+'Backup Log '+@db+' with truncate_only;'
SET @sql=@sql+'DBCC shrinkfile ('+@logname+', 2);'
--print @sql
EXEC sp_executesql @sql,N'@logname varchar(300)',@logname

fetch cDB into @db
end
close cDB
deallocate cDB