SQL Server Management Studio Top 10 Productivity Tips and Hidden Secrets

By: Joe Gavin   |   Updated: 2022-03-31   |   Comments   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > SQL Server Management Studio

Problem

You're using Microsoft SQL Server Management Studio (SSMS) and would like to become more productive with this IDE. Check out these tips on how to make the most of SSMS.

Solution

In this tutorial, we'll look at 10 handy productivity tips and hidden secrets that will help you save time for database administration and development. We'll be using SSMS 18.11.1 and will connect to a SQL Server 2019 database engine which are both the latest versions as of the time of writing. These items will work with older versions of SSMS as well as other versions of SQL Server.

Just in case you need it, here is how to download SQL Server Management Studio and here is how to install SQL Server Management Studio.. Let's get started.

Ten Tips

Following are the 10 SSMS tips for DBAs and Developers:

1. Dragging and Dropping Object Names from Object Explorer to a Query Window
2. Registered Servers
3. Block Highlighting in the Query Window
4. Switch Query Tabs
5. Upper Case and Lower Case Text
6. Cycle Clipboard Ring
7. Use Database Dropdown
8. Switch between Results and Messages Tabs
9. Comment and Uncomment Selected Text and T-SQL code
10. Open Integrated Web Browser

Dragging and Dropping Object Names from Object Explorer to a Query Window in SSMS

This SSMS functionality lets you drag most database object names from the Object Explorer to a Query Window. This can save a lot of typing and typing mistakes.

In this example we're going to select all columns from the dbo.Categories table in the sample Northwind SQL Server database.

1. Expand the server dropdown
2. Expand Databases
3. Expand Northwind
4. Expand Tables
5. Expand dbo.Categories
6. Click and hold Columns and drop after the SELECT
7. Click and hold database name and drop after the FROM

Registered Servers in SSMS

Registered Servers let you:

• Preserve connection information for SQL Server authentication and Windows authentication
• View service status
• Connect Object Explorer and Query Editor
• Organize your SQL Server by category in groups, i.e. development, test, production, geography, data center, Windows Server, Linux, etc.
• Display a more user-friendly server name in the management tool
• Provide detailed server descriptions
• View the SQL Server log files for online or offline SQL Server instances

To register a SQL Server, Ctrl + Alt + G or:

1. View
2. Registered Servers

1. Right click on Local Server Groups
2. New Server Group…

1. Name Group and optionally add a Description
2. OK

1. Enter Server name
2. Test

Verify test was successful.

1. Save

If you're adding a significant number of Groups and / or SQL Servers to Registered Servers, it's a good idea to back up the file that holds them.

1. Right click on top group
2. Tasks
3. Export…

1. Browse to directory and enter file name
2. OK

1. Choose directory and enter file name
2. Save

1. OK

Block Highlighting in the Query Window in SSMS

If you've ever had a vertical block of text to edit you've probably asked if there is an easy way to do that. The answer is yes, there is.

Place the cursor where you want to begin. Hold the Shift and Alt keys and then highlight area to edit with the arrow keys or mouse to highlight a block.

Enter or paste text into block just once to change every line in one action.

Switch Query Tabs in SSMS

It's not uncommon to have quite a few Query Windows open at the same time. Clicking through them can become harder as the tabs get smaller as more windows are opened. Ctrl + Tab will open a window with a list of Query Windows.

Continue to Ctrl + Tab until you land on the Query Window you want. And Ctrl + Shift + Tab will move through the list in reverse.

Upper Case and Lower Case Text in SSMS

It's easy to make text all upper or lower case.

This screenshot shows an ‘EXEC sp_helpdb' that was typed in using the shift key, but caps lock was on.

There is no need to retype it. Simply highlight the lower case text you want to change and Ctrl + Shift + U to make it all upper case. Highlight the upper case text to change and Ctrl + Shift + L to make it lower case.

Cycle Clipboard Ring in SSMS

Here I've separately cut 3 lines of text. We know a Ctrl + V will paste the last line. What if I want to paste a line from the clipboard that wasn't the last line?

Ctrl + Shift + V will cycle through each line in the clipboard you get the one you want.

Use Database Dropdown in SSMS

If you want to change your database context from the dropdown it's easy enough to click on it and select. A quicker way is to use a hotkey combination. Ctrl + U will bring you directly to the box where you can arrow up and down arrow keys to select your database. This is handy if you're connected to an Azure SQL Database server and can't use a ‘USE database_name;'.

Switch between Results and Messages Tabs in SSMS

Rather than mouse click on the Results and Messages tabs you can use the F6 key to switch back and forth between them.

Comment and Uncomment Selected Text in SSMS

SSMS gives you the ability to comment and uncomment highlighted text.

For this example, we have two simple queries and want to comment the first one out to be sure we don't run it.

Highlight the first query's text, then Ctrl + K + C to comment each line out. To uncomment, it's a Ctrl + K + U.

Open Integrated Web Browser in SSMS

It's not as full featured and you won't get the same experience as you would using a regular browser but sometimes it's handy to have a browser within SSMS to avoid the visual distraction of switching applications. Ctrl + Alt + R will open it in an SSMS tab. You can Ctrl + Tab between it and your Query Windows.

Hotkeys in SSMS

Following is a list of hotkey related actions shown above and their associated hotkey as a reference. See links below for more articles on SSMS shortcuts.

Next Steps

Here is a link to a list of SSMS MSSQLTips:

• SQL Server Management Studio Tips
• SQL Server Management Studio Configuration Tips
• SQL Server Management Studio Shortcuts Tips
• How to Connect to a SQL Database including Authentication
• Download SQL Server Management Studio
• How to Install SQL Server Management Studio on your Local Computer
• Step by Step Installation of SQL Server Management Studio 18.6

About the author

Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips