Seven secrets of SQL Server Management Studio

Multiplicity

The Object Explorer Details pane doesn’t show up by default, but you’ll definitely want to know how to summon it.

Connect to your SQL Server instance, then in the object explorer pane drill down and highlight a container, such as Tables. Then click View => Object Explorer Details from the top menu bar.

Most people know that by highlighting an object (eg an SQL Agent job) and right-clicking, an option presents itself to generate a script of the said job. However it is only possible to script one object at a time – so for five jobs, repeat this process five times.

The Object Explorer Details pane allows you to select multiple objects - by using the Ctrl or Shift keys, and then right-clicking to select an option to script all objects in one hit.

Need to script 1000 objects? Do it this way and avoid RSI to your mouse-pinkie.

Desperate measures

With the Tables container still highlighted, you’ll see that over in the Object Explorer Details pane along with the column containing the table names, there are some other uninspiring columns such as Schema, Policy Health State, and Create Date.

However, by right-clicking anywhere on the column headers, you can expose much more interesting hidden columns for inclusion. For the Tables container these include Row Count, File Group, and Data Space Used.

By left-clicking on any of these columns, you can sort the displayed objects by them. So questions like “What are the largest tables?” are now answerable in a couple of mouse-clicks.

For the Stored Procedures container a great column to add is Date Last Modified. Invaluable when you suspect that code has changed, but no one will admit to it.

The other guys

There may be no such thing as a free lunch, but there are free third-party add-ins that provide new or enhanced functionality to SSMS.

You can improve SSMS’s code auto-completion; format code nicely; search a database and then search it again; and enhance query plan analysis.

Out of sight

If you’ve examined a long piece of T-SQL in SSMS, you’ve likely scrolled up and down through it many times. Perhaps a large block of variable declarations is at the top, and you need to keep referring to it in order to understand the code below.

Click Window => Split from the menu-bar at the top of SSMS, and your query is spilt into two independently scrollable panes. You can keep variable declarations in the top-most pane, whilst scrolling through the code in the lower pane

If you need to compare two different panes of T-SQL, don’t click back and forth between query tabs trying to remember what you saw. Just click Window => New Vertical Tab Group and both panes of T-SQL will co-exist together side-by-side for your viewing pleasure.

Need for speed

Can you remember the different style codes available within the CONVERT function? Me neither, let’s look them up.

We could minimise SSMS, open the browser, see we have new Gmail, open it, and view the video of a skateboarding duck that Bob sent. Go to Google, type in “T-SQL CONVERT”, and then scroll through the results to look for something relevant to click on.

Or we could just highlight CONVERT in SSMS, press the F1 key, and be taken straight to the authoritative information in the online documentation.

Touch and go

Imagine you’ve been emailed hundreds of values that you need to incorporate into a T-SQL query. You can copy and paste the values into a query pane in SSMS, but you’ll still need to manually navigate through each value and add a comma to them.

Wouldn’t it be nice if you could add characters and commands to each line of a query en masse?

Move the cursor to the starting position (in our example, just in front of the second value in the list), then press and hold down the left mouse button. At the same time press and hold down Alt and drag the cursor down until it arrives just in front of the last value in the list.

If your finger-origami is good, then a feint line will materialise.

Release both buttons. Now whatever character(s) you type will appear in that same position on each line. When you’ve finished typing, hit the Esc key to exit multi-edit mode.

A different approach

Most people know that by dragging something like a column from the Object Explorer pane into a query pane, its name will appear within the query, removing the need to type it.

But if you expand a table in Object Explorer, and then drag the Columns container into the query pane, a nice comma separated list of every column in that table will be added to the query.

If you don’t need every single column in your query then it’s easy to delete ones you don’t require. This prevents typos and also avoids the dreaded shortcut SELECT * FROM.

About the author

Andy Hogg is a freelance consultant specialising in Microsoft SQL Server and associated technologies