March 4, 2014 Leave a comment
I still can get enthusiastic when I discover tiny new features of SQL Server Management Studio (SSMS). It’s the tool that I use every day, but still I discover new and cool things to use, that I never noticed before.
Filter Object Explorer
If you look at the Object Explorer, you’ll see a little button that can come in quite handy: the filter button:
This allows you to filter the results in Object Explorer. If you click on it, you will see the filter window pop up:
Just as a test, I filtered my object on name contains “Test”. This will filter only the database and object type you selected. In my case, I selected the Table-node, and it will filter only these objects:
There are a few drawbacks on this. One of them is that you can’t remove the filter without opening the filter pop-up again. Another one is you only filter results once. If you want to adjust your filter, you need to remove it completely, and reapply your new filter.
One of the things I use on a regular basis is the “Generate and Publish Scripts” wizard. But did you know this wizard had some hidden gems? One of the options you have, it to script objects to individual files. That’s an option that is hidden in plain sight:
But another gem is hidden behind the Advanced button:
This allows you to generate insert scripts for you tables, without the use of a 3rd party tool.
One of the hidden gems I wanted to show you, isn’t one I found out myself. This one I discovered via a blog post from Kendra Little (Blog | @Kendra_Little). She blogged about the Splitter Bar in SSMS, which is quite handy sometimes! Go check out her other blog posts as well, for example about Scripting changes from the GUI.
In the picture above you see the same stored procedure (in this case from the AdventureWorks 2012 database), split in 2 by the splitter bar. This makes it for example easier to look at the declare statements in the top of the script, and the query your working on in the bottom of the script.
The last one is one I use on a regular basis. At the past few employers, I’ve worked with 2 monitors. This makes it easy to compare files or result sets. But what if you don’t have that luxury? There’s an option in SSMS to create a new tab group. Just right-click a query tab, and choose the option you like:
Let’s say you want to compare the resultsets of 2 queries, you can use the Horizontal Tab Group option:
To return to your normal view, just right-click on the tab again, and click “Move to Previous Tab Group”.
One of the things Mickey Stuewe (Blog | @SQLMickey) pointed out, is that you can rearrange the columns in the result window of SSMS. Just drag and drop columns the way you like. It could save you a lot of time rerunning the query to change the order of your columns. The order of the columns is reset the next time you run the query.
Never stop learning!
There are plenty more hidden gems in SSMS, waiting to be found by you. So never stop learning, and always try to take it a step further than needed. You’ll be surprised to see what awaits you…