Top 10 SQL Server Management Studio (SSMS) Tips and Tricks

Category : Articles Published : December 11, 2014 User Rating : 4.5 Stars      Views : 4.5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.

I thought I’d share a few tips and tricks that I’ve picked up along the way with SQL Server Management Studio. These are some of the features which I’ve found to be useful, but which are perhaps less well known. I’ve not included any T-SQL features as this article is specifically about SSMS. These are my top features but if you regularly use SSMS I'm sure you will know of at least some of these, I'd also be interested to know of any other useful features that you use. Please reply in the comments section below if you can add any other tips.

1. Splitting the Query Window

I've already covered this in another article (see : Splitting the query window in SQL Server Management Studio) but the query window in SSMS can be split into two so that you can look at two parts of the same query simultaneously. Both parts of the split window can be scrolled independently. This is especially useful if you have a large query and want to compare different areas of the same query. To split the window simply drag the bar to the top right hand side of the window as shown below. SSMS Split Query Window

This leads onto to another feature which is especially useful for larger queries, which is line numbers :

2. Adding Line Numbers

It can be useful to add line numbers to the query window, especially for larger queries. To switch on the display of line numbers click on the Tools -> Options menu item. In the dialog box that appears click on Text Editor and then Transact-SQL in the left hand pane and check the Line numbers check box in the right hand pane. Line numbers
Line numbers will then appear in each query window. You can also go to a specific line number by pressing CTRL + G which will open the Go To Line dialog :
Go To Line Number

3. Moving columns in the results pane

It may not be immediately obvious but you can switch columns around in the results pane when using the grid view, by dragging the column headers and dropping them next to another column header. This can be useful if you want to rearrange how the results are displayed without amending the query, especially if you have a lot of columns in your resultset.

4. Dragging a comma separated list of all Columns in a Table from Object Explorer window to the Query Window

If you want to list all the columns in a table as a comma separated list (perhaps as the starting point for a SELECT clause) simply drag the ‘Columns’ item in Object Explorer and drop it onto to a query window. A list of all columns separated by commas will be displayed in the query window, as shown below. Drag a list of all columns in a table to a query window

5. Using GO X to Execute a Batch or Statement Multiple Times

The ‘GO’ command marks the end of a batch of statements that should be sent to SQL Server for processing, and then compiled into a single execution plan. By specifying a number after the ‘GO’ the batch can be run specified number of times. This can be useful if, for instance, you want to create test data by running an insert statement a number of times. Note that this is not a Transact SQL statement and will only work in Management Studio (and also SQLCMD or OSQL). For instance the following SQL can be run in SSMS :

INSERT INTO TestData(CreatedDate) SELECT GetDate()
GO 10
This will run the insert statement 10 times and therefore insert 10 rows into the TestData table. In this case this is a simpler alternative than creating a cursor or while loop.

6. Selecting a block of text using the ALT Key

By holding down the ALT key as you select a block of text you can control the width of the selection region as well as the number of rows. There are a couple of situations where I’ve found this to be very useful. The first is to delete a block of text, for instance if you want to delete all the schema/table references in the column list below, simply highlight the table references while pressing the ALT key, so that the text shown below is highlighted.
ALT Select 1
Then press the delete key to delete the block :
ALT Select 2
This can also be used to replace or insert a block of text. For example to add the schema reference back in, block select a column of zero width by pressing the ALT key and selecting an area 4 rows high and zero columns wide immediately before the column names. The selection will be indicated by a pale grey line as shown below :
ALT Select 3
Any text you now type will appear in all four rows simultaneously. In the screenshot below I’ve typed in ‘Department.’ :
ALT Select 4

7. Colour coding of connections

SQL Server Management Studio has the capability of colouring the bar at the bottom of each query window, with the colour dependent on which server is connected. This can be useful in order to provide a visual check of the server that a query is to be run against, for instance I like to colour code production instances as red, development as green and amber as test. This can also be used in conjunction with Registered Servers and CMS (Central Management Server). To add a colour bar when connecting to the server click on the Options button in the Connect to Database Engine window and then select the Connection Properties window. Select the check box towards the bottom of the window and use the ‘Select…’ button to choose a colour.
Coloured Server Connection 1
That colour is then associated with the connection and is used to colour the panel at the bottom of the query window :
Coloured Server Connection 2

8. SQLCMD mode

Switching on SQLCMD mode enables a number of useful extra scripting style commands in SSMS. In particular you can use it to change to the connection credentials within the query window, so that you can run a query against multiple servers from the same query window. There are more details of how to do this here : Changing the SQL Server connection within an SSMS Query Windows using SQLCMD Mode

9. Registered Servers / Central Management Server

If you have a lot of servers then re-entering the details in Object Explorer every time you start SSMS can be frustrating and time consuming. Fortunately there are two facilities within SSMS that enable these details to be entered just once and “remembered” each time you open up SSMS. These two facilities are Registered Servers and Central Management Servers. These were introduced in different versions of SQL Server and work in different ways, each has its own advantages and disadvantages so you may want to use both.

To add a registered server open the Registered Servers window from the View menu (or click CTRL + ALT + G), the window should appear in the top left corner of SSMS. Right click on the Local Server Groups folder and select ‘New Server Registration…’. Enter the server details and close the window. This new server should then appear under Local Server Groups, you can then right click and open up the server in Object Explorer or open a new query window. The server details are stored locally in an XML file and so will appear next time you open SSMS. If you have a lot of servers then you can also create Server Groups to group together similar servers. One advantage of creating groups (other than being able to logically group similar servers together) is that you can run a query against all servers in the group, by right clicking the group and selecting ‘New Group’.

Central Management Server are similar to Registered Servers but with some differences, the main one being that the server details are stored in a database (the Central Management Server) rather than a local file. A significant limitation with CMS is that the CMS server itself can’t be included in the list of servers.

There are some differences in capabilities between Central Management Servers and Registered Servers so you may want to investigate that before choosing which system to use, or (as I mentioned above) use both. A brief summary of the differences is below :

ItemRegistered ServersCentral Management Server
Method for storing connectionsLocal XML fileSQL Server (msdb database)
VisibilityCurrent user onlyAll users (subject to being given access permissions)
Types of connections accessibleSQL, SSAS, SSIS, SSRSSQL only
AuthenticationWindows and SQL ServerWindows only

10. Script multiple objects using the Object Explorer Details Windows

Individual database objects, such as a table or stored procedure, can be scripted within SSMS by right clicking on the object within Object Explorer and selecting the appropriate item in the drop down menu. However if you have a lot of objects to script that can quickly become time consuming. Fortunately it’s possible to select multiple objects and script them up all together in a single query window. To do this just open the Object Explorer Details window from the View menu (or press the F7 key). If you want to script up multiple (or all) tables, select the Tables item under the relevant database in Object Explorer. A list of all tables appears in the Object Explorer Details window. Select the tables you want to script (using the Control key if necessary) and then right click and select which script option you want – e.g. to create a table create script for all tables. This is shown in the screenshot below : Script Multiple Objects

Link back to this article : 10 SQL Server Management Studio (SSMS) Tips and Tricks.aspx


SQL 2012, 2008, 2008R2,SSMS,SQL Server,Management Studio

Post by Pedro S Faro on Tue 20 Oct 2015 18:03. Report Inappropriate Post

Congratulations for tips . Some of them i didnt' know.

SSMS is a great tool, but one think makes me nervous, that is to copy a result of a query to EXCEL.
If you "select all" "copy & paste" , Excel convert fields like 011 to 11 and it's not a soluition.
Other way is to save in CSV , and call CSV file ... too much work.
Or use the OLD" interface of EXCEL(microsoft query ...) ... to much work to configure..

I would like to know a fast way to put a result of a query in EXCEL , like i see in SSMS.



Post by SQL Matters on Tue 20 Oct 2015 21:32. Report Inappropriate Post

Thanks for your comments.

It sounds like Excel is treating the value as a number, and stripping off any leading zeroes. I've not tried this, but before pasting the data if you select all the cells in the spreadsheet and format every field as text then maybe it will preserve leading zeroes ? It's worth trying anyway.
Post by Pedro S Faro on Tue 20 Oct 2015 21:46. Report Inappropriate Post

Hi ,
Tank's for the tip.

For that case it works, but there is more situations.. When you pass money(2 decimals) values for SSMS , it passes with 11.00. Since excel works with ",",i've to replace "." with "," to excel assume Numbers ..

What i mean is why SSMS , doesn't have a option to copy directly to EXCEL and only have CSV option..


Post by SQL Matters on Wed 21 Oct 2015 10:12. Report Inappropriate Post

Thanks for the reply. An SSMS export to Excel would be useful, but I guess they wanted to keep things simple - although CSV has limitations it is supported by all versions of Excel, so there are no compatibility issues with versions and file formats etc. Another thought, you could use the Import and Export Wizard in SSMS to extract the results of a query directly to an Excel spreadsheet, though I appreciate that's probably not really the solution you want.
Post by Firmbyte on Sun 08 Nov 2015 21:40. Report Inappropriate Post

Your article again proves that with SQL Server, every day is a school day: Item 6 was a new one for me.
Thank you!
Post by chuck on Tue 22 Dec 2015 17:17. Report Inappropriate Post

Number 6 is awesome.....
Post by Jessica on Tue 06 Sep 2016 20:02. Report Inappropriate Post

I can't get the second part of #6 to work :(
Post by Curtis on Tue 28 Mar 2017 15:03. Report Inappropriate Post

For item 6, hold down Alt + Shift then move your keyboard arrows around selected area to get this to work. But great tips, thanks !
Post by Sam on Fri 31 Mar 2017 15:16. Report Inappropriate Post

# 6 works in most text editors in windows (not notepad). #4 will be useful for sure.

Is there a way to copy multiple result sets with one copy command. For example, if multiple queries are run in one query window, such as :

select 1
select 1, 2
select 'A', 3, 4

three result sets are produced in one tab or window, but to copy them it is necessary to copy each one separately.
Is there a way to copy all of them at once?
Post by Jim Johnston on Mon 14 Aug 2017 16:27. Report Inappropriate Post

Why don't you format the Excel table exactly how you want the data to be formated. Only then will you get a good import of a CSV file. Don't fight with the data after the import, that is crazy man! Take one row and the format all the cells using it. .
Post by Metodija Angjelkoski on Wed 23 Aug 2017 13:23. Report Inappropriate Post

This is nice :)
Thanks for sharing
Post by Rduggu on Mon 09 Oct 2017 20:47. Report Inappropriate Post

Thanks for sharing. Very helpful!
Post by jan-Willem on Tue 23 Jan 2018 13:49. Report Inappropriate Post

Yes, Dragging with the alt key is my best friend now :)
Came across your blog some time ago and have used your #6 option many, many times since then.
Thank you so much.
Post by sophie.xu on Tue 27 Feb 2018 07:15. Report Inappropriate Post

to Pedro S Faro, save to excel, an alternate way i used is to use SSRS, create a tablix and then export data to csv or excel. it may take some time to create report, but you can extract so much data with correct format, i prefer this when save to csv or excel not works in SSMS directly
Post by sophie.xu on Tue 27 Feb 2018 07:18. Report Inappropriate Post

another way we used to Script multiple objects is to right click on that database and select tasks/generate scripts…/(select objects which needed), it will save scripts to a file to a location appointed

and so much thanks to Master, these 10 tips are helpfull. thanks a lot!
Post by katt on Thu 05 Apr 2018 15:17. Report Inappropriate Post

Good stuff thanks. Why is the object explorer window not getting any cross hair so it can be resized from right to left? I want to reduce the width of the object explorer window but am unable.
Post by Tony on Tue 12 Jun 2018 11:25. Report Inappropriate Post

Thank for the post! I'd like to add one more solution - Developer Bundle
This is a tool pack that extends SSMS with many essential features aimed not only boost developers' productivity but also reduce expenses while performing routine tasks.
Post by r phillips on Wed 20 Jun 2018 13:22. Report Inappropriate Post

yeah, Developer Bundle is only $800+... whatevs...
Post by Steve on Fri 08 Feb 2019 20:14. Report Inappropriate Post

When I paste results to Excel I just paste first then format any columns that need to text fields and paste again.
Post by Ryan on Wed 03 Jul 2019 19:31. Report Inappropriate Post

Excellent info, thanks for sharing!

Post a comment   No login required !

Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered