My favorite SSMS tips and tricks

61

In this post I cover my favorite SSMS tips and tricks that I’ve picked up along the years, and on which I rely on a daily basis in my workflow.

If you’re interested in my SQL Server Management Studio configuration recommendations – check this post out. This also contains how to have two rows of Query Editor tabs, so I’m not covering that here again.

You can find the latest version of SSMS on the official download page.

Object Explorer

SSMS’s Object Explorer comes with some often overlooked features that can make life a lot easier.

Object Explorer Details (F7)

One of these features is Object Explorer Details that can be accessed by pressing the F7 key.

This is a list of info that I like using Object Explorer Details to get.
Note that this isn’t an exhaustive list, but a list of my most common use cases.

Databases Overview

To get an overview of the databases on the instance I’m connected to, I highlight/click once on the Databases folder in Object Explorer and press F7.

This opens the Object Explorer Details tab showing the databases on the current instance and information pertaining to them.

I’m usually interested in database size, state, recovery model, compat level.

Back when I was a prod DBA, I also liked seeing the Last Backup and Last Log Backup dates, and if silly options like auto-shrink or auto-close were enabled.

I can easily sort this list by clicking on the column that I want to use to order the results by. In this example I’ve sorted by Index Space Used descending.

The list of columns displayed here is also customizable by right clicking on the column header you can select columns you’re more interested in and deselect columns you don’t need.

This is the full list of columns available for databases’ details.

Tables overview for the whole database

In the same vein as the Database Overview example, you can use F7 to get relevant information about tables.

For this, I highlight the Tables folder of any database and press F7.

Again, columns, their order, and the order of the output, can be customized depending on various needs.

Indexes and statistics overview

Going a level deeper, I expand a table in OE, highlight the Indexes folder and press F7 to get an overview of the indexes.
I can then tweak the columns as in the previous examples to see info that’s more relevant.

The same method can be applied for statistics, the only gotcha being that the info shown for statistics is fairly limited.

Note that, if you already have Object Explorer Details opened, you don’t need to press F7 every time, you can just navigate in OE to the relevant group and the Details view will refresh accordingly.

Drag and drop object names

This is one of the features that, once people learn about, they rarely end up not using it.
Especially when most of the work consists of writing queries.

From Object Explorer, you can drag pretty much any object and drop it into the Query Editor to avoid having to manually type it.

This applies to:

• database names
• any type of database object (including users and roles)
• linked servers
• logins
• server roles

This feature really shines when you have to list all the columns in a table for a query.
By dragging the Columns folder and dropping it into the Query Editor you’ll get a comma-separated list of all the columns from said table.

Other table-level folders this works for:

• Triggers
• Constraints
• Statistics
• Keys (somewhat since they’re not comma-separated and don’t even have a space between them

For some reason it doesn’t work for index names, at least not yet (tested with SSMS 19.2 to make sure).

Open a query editor tab right in the context of a specific database

If you highlight a database in Object Explorer and then press Ctrl+N (new query) SSMS will open a Query Editor tab in the context of the highlighted database.

Query Editor

View table info with Alt+F1

I use this a lot when I’m doing performance tuning work and I have the offending query opened in the Query Editor.

I select a table name in the query and then press Alt+F1 this runs sp_help for the selected table and returns information such as:

• Table name, owner, type, and creation date
• Information about the columns
• Identity column info – if defined
• RowGUID column info – if defined
• Filegroup name
• Index names, descriptions, and key columns
• Information about constraints
• List objects referencing the table

Switch database used by the Query Editor

If you have a Query Editor tab opened and want to switch the database it uses without having to use your mouse, you can do that via the following steps:

• Press Ctrl+U to highlight the Available Databases menu
• Either use the Up or Down arrow keys to navigate through the list of databases or just start typing the name
• Press Enter when the desired database is selected

And now your Query Editor window is using another database.

Easily comment and un-comment code

I’ll preface this by saying that I’m generally against line comments in production-ready code.

The reason is, when doing performance tuning work and grabbing the query from either the plan cache or from the query store, the formatting might not be preserved and and the query ends up being all one long line, thus line comments make it pretty much impossible to figure out which portions were supposed to be commented out and which ones were valid.

That being said, the following is my go-to method for commenting out blocks of code when I’m working on a query and testing something out.

• Comment one or more lines of code by selecting the line(s) and pressing Ctrl + K, Ctrl+ C – hold Ctrl tap K and afterwards, while still holding Ctrl, tap C.

• Un-comment one or more lines of code by selecting the line(s) and pressing Ctrl + K, Ctrl+ U – hold Ctrl tap K and afterwards, while still holding Ctrl, tap U.

Split query editor window horizontally

I tend to do this when working with long scripts where I need to constantly navigate between the end of the script and the beginning or somewhere in the middle.

This allows me to have two regions of the same Query Editor tab visible at the same time.

To do this you need to click on the tab with the split bi-directional arrow symbol on it that’s at the top of the Query Editor’s vertical scrollbar (upper right corner) and drag it down.

Depending on your scroll mode option, either map or bar, the tab can look a bit different, so here’s an image showing it in both flavors of the vertical scroll bar.

This splits the current Query Editor tab horizontally, allowing two separate regions of the script to be visible at the same time and independently scroll-able.

Here’s an example viewing a script that’s part of PSblitz in an horizontally split Query Editor window.

Multi-line/block editing

This is a great time-save when having to edit multiple lines in a similar way.

Multi-line or block editing makes use of the Alt key in combination with either Shift and arrow keys, to extend your cursor to multiple lines, or click and drag to select strings from multiple lines (without the extra white space in case of uneven lines) to copy and paste those strings in other places.

In the above example I do the following:

1. Alt+Shif+Down arrow to expand the cursor to all the lines
2. Start typing the command while leaving an extra space between INTO and FROM
3. Go to the end of the lines and use Alt+Click and drag to select the table names
4. Paste the table names between INTO and FROM
5. Use Alt+Shift+Down arrow again to edit the names of all three target tables

Cycle through SSMS’s clipboard ring

This is probably one of the most useful copy-paste tips related to SSMS, especially since it saved my bacon a couple of times when the usual “one slot” clipboard would have caused me to lose important info.

SQL Server Managements studio stores its own clipboard history, called the Clipboard Ring.

It can accommodate 20 entries, ordered from newest to oldest, of the stuff you’ve been loading into the clipboard during the current session of SSMS.

To cycle through the Clipboard Ring just hold Ctrl+Shift and tap V.

You can also do this via the Edit menu.

Note, the clipboard ring is only available for stuff you’ve copied inside of SQL Server Management Studio, anything else is toast once you’ve overwritten the default Windows clipboard.

Regex find and replace in SSMS

Granted I don’t take advantage of this as much as I’d like, but this is because I’m generally bad at regex.

This still does come in handy in cases like splitting comma-delimited column lists into separate lines.

To access this from the regular Find and Replace menu (Ctrl+H) either tap Alt+E or click on the Regex symbol.

The above example will replace all commas with commas preceded by a new line character, splitting a comma-delimited column list into one line per column.

Using GO to execute the same statement(s) multiple times

I have have a bunch of use cases for this, but my most notable are:

• executing sp_BlitzWho repeatedly when I’m monitoring activity
• inserting a bunch of dummy data into a table, but I don’t care enough about the randomness of the data and/or don’t have time to write a WHILE loop
• when testing PSBlitz, I have a bunch of scripts that run various queries and stored procedures to generate test data (index usage, execution plans and stats, locking, blocking, etc.) I want each of those queries/stored procs to be executed multiple times without me having to copy-paste them multiple times (yes, I know I can use SqlQueryStress for this, yet I don’t 🙂 )

The trick is to add a number (equal to the number of times you want that batch to be executed) right next to the GO

Here’s a quick example of a print statement that gets executed 3 times.

Reordering result set columns

You can click and drag columns in the result set to rearrange them. I use this mostly for screenshots with outputs from stored procedures like sp_BlitzWho or sp_BlitzCache.

Rearrange Query Editor tabs

You can click on a Query Editor tab and drag it to:

• change the tab order
• pop it out of the SSMS window altogether so you can move it on a separate screen
• have two or more tabs displayed at the same time either side by side or stacked

When you start dragging a Query Editor tab, the following overlay will appear in the center of SSMS.

Dragging the tab to one of the symbols will position the tab according to the orange-ish (I think) portion in of the selected symbol.

Dragging the tab anywhere outside of the overlay will detach it from SSMS, or from its original Query Editor group.

Generating scripts

Scripting multiple indexes

This comes in very handy when I’m making changes to existing indexes and I want to have backup scripts of some or all of the indexes on a specific table.

Remember the tip about getting an index overview for a table using the Object Explorer Details feature (F7)?

This uses the same thing, but, this time, instead of just looking at index info we can use OE Details to generate scripts for multiple indexes on a specific table.

I do this by selecting (either via Shif+Down/Up arrow key or click and drag) the tables I want the DDL for, and then right clicking on the selection. Afterwards I click on Script Index As from where I can pick the option that best fits my use case.

Generate scripts to insert data

This is fairly similar to my previous post, as in it’s a way to export/import data out of/into multiple tables.

But it does that via SSMS by generating an insert statement for every record in the selected table(s).

The steps are like the ones to generate a DDL script for one or more tables, with one minor difference.

First, to get to the Generate Scripts wizard:

1. Right click on a database
2. In the context menu click on Tasks
3. Then click on Generate Scripts…

Then, in the Generate Scripts wizard I click on “Select specific database objects”, I expand Tables, check the tables I want, and click Next.

And this is where difference comes in.

In the Set Scripting Options section, I click on Advanced

Now, in the Advanced Scripting Options menu, I scroll down in the General section and change the Types of Data to Script option from “Schema Only” to “Data Only” and click Ok.

This tells the wizard to generate the insert statements for all the records in the selected tables without also generating the DDL commands to create said tables.

After that I select my preferred scripts saving options.

In this case save one script file (.sql) per table.

And then click Next, Next, and Finish to initiate the script generating process.

Registered Servers

Query multiple instances at the same time

This is one trick I used a lot during my time as a full-time production DBA.

Mainly in cases where app owners were uncertain on which instance their application’s database resided and occasionally even the names of their databases only giving me a partial name to go by.

This requires two things:

1. To either use local Registered Servers (this are SQL Server instances registered in your SSMS) or use Central Management Servers (this uses a separate SQL Server instance to store information about registered SQL Server instances from within your environment).
2. To have the Registered Servers pane opened – you can do this by pressing Ctrl+Alt+G

In my demo I’ll use local registered servers, because those can also use SQL Authentication, while CMS uses only Windows Authentication and Microsoft Entra Authentication (formerly Azure AD). But the behavior is the same.

First, I right click on the Local Server Groups folder and click on New Query. I can also highlight the folder and press Ctrl+N.

Then, the newly opened Query Editor window will be connected to all the registered instances and I can use it to run the following query.

Which returns this result set.

Notice that it contains an additional column with the name of each server where a matching result was found.

Conclusion

SQL Server Management Studio offers a bunch of features and options that allow users to optimize their workflow.

This isn’t an exhaustive list of SSMS tips and tricks by any means, and I’m certain there are more that I don’t even know of yet, but these are the ones that I use(d) on a regular basis.