SQL Server Management Studio Top 10 Productivity Tips and Hidden Secrets

By:   |   Updated: 2022-03-31   |   Comments   |   Related: > SQL Server Management Studio


   Free MSSQLTips webinar - "Efficient Monitoring and Management of SQL Server" (click to register)

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
Dragging and Dropping Object Names from Object Explorer to a Query Window

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
Registered Servers
  1. Right click on Local Server Groups
  2. New Server Group…
New Server Group
  1. Name Group and optionally add a Description
  2. OK
Name Group
  1. Enter Server name
  2. Test
Test Connection

Verify test was successful.

Connection Successful
  1. Save
Save Registered Server

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…
Backup Registered Servers File
  1. Browse to directory and enter file name
  2. OK
Browse to Backup Folder
  1. Choose directory and enter file name
  2. Save
Name File
  1. OK
Export to File

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.

Select Text Block

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

Edit Text Block

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.

Switch Query Tabs

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.

Change Text Case with Hotkey

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?

Cut Text

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

Cycle Clipboard Ring

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;'.

Hotkey to Database Dropdown

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.

Hotkey Between Results and Messages Tabs
Messages Tab

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.

Test to Comment

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

Highlight and Comment

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.

Open Integrated Web Browser

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
Next Steps

Here is a link to a list of SSMS MSSQLTips:




Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips


Article Last Updated: 2022-03-31

Comments For This Article





download














get free sql tips
agree to terms