It’s a well known fact that any sitcom that runs for a certain length of time will release a clip show. Similarly any blog about SQL Server will end up doing a top tips for using SQL Server Management Studio. Here then, in no particular order, are mine:

1. Filtered views in object explorer.

This is a great feature for navigating around large unfamiliar databases. Available on tables, views, stored procedures and functions. Simply right click the node in the object explorer:

Enter your filter conditions into the filter setting dialog and you can zoom in on the objects you need quickly:

Object explorer will helpfully indicate that the view is filtered so you don’t think you’ve lost a bunch of tables!

2. Object explorer details.

I’ve overlooked this in the past – but it’s quite handy for a quick look at object properties. Available from the view menu or by pressing [F7]. The objects in this view can be filtered using the tip above. By right clicking on the column headers you can select the properties you want to see.

Column headings can be rearranged by dragging and dropping the headers and the rows can be sorted ascending or descending by column.

I use this a lot to get a quick look at what is consuming space in the database. What are my biggest tables? How many rows in a given table?

3. Drag column names.

We all know that we can drag individual items from the object explorer into the Query window – but you can also drag the column node and get a nice comma separated list of column names.

All right – it’s a bit annoying being all in one line like that. But while it’s still highlighted press [ctrl]-[h] to get the find and replace dialog:

Make sure you change the ‘look in’ drop down to ‘selection’ (or you can get some strange results). Expand the find options node and select Use > Regular expressions. In ‘find what’ put [,] & [space] and in ‘replace with’ put [,] & [\] & [n].

For an extremely comprehensive look at using regular expressions in SSMS look here.

4. Block move.

Finally, if you have your column names all on a separate row from the tip above, but you want them indented you could manually enter a [tab] at the start of each row (like the good old days) or you could use a regular expression find and replace as above. But, let’s face it, no one wants to deal with those things if we don’t have to. The third option is to hold the [alt] key down and drag across the first few characters of your column name and down the rows. Once you have the block selected simply press [tab] to indent.

There’s a million tips and tricks in SSMS. I have shown you 4 of my current favourites. Feel free to share yours…