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.
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.
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.
- Expand the server dropdown
- Expand Databases
- Expand Northwind
- Expand Tables
- Expand dbo.Categories
- Click and hold Columns and drop after the SELECT
- 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:
- View
- Registered Servers
- Right click on Local Server Groups
- New Server Group…
- Name Group and optionally add a Description
- OK
- Enter Server name
- Test
Verify test was successful.
- 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.
- Right click on top group
- Tasks
- Export…
- Browse to directory and enter file name
- OK
- Choose directory and enter file name
- Save
- 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.
| Action | Hotkey Combination |
|---|---|
| Block Highlighting in the Query Window | Shift + Alt + Arrow keys |
| Switch Query Tabs | Ctrl + Tab / Ctrl + Shift + Tab |
| Upper Case and Lower Case Text | Ctrl + Shift + U / Ctrl + Shift + L |
| Cycle Clipboard Ring | Ctrl + Shift + V |
| Use Database Dropdown | Ctrl + U |
| Switch between Results and Messages Tabs | F6 |
| Comment and Uncomment Selected Text | Ctrl + K + C / Ctrl + K + U |
| Open Integrated Web Browser | Ctrl + Alt + R |
