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 :
CREATE TABLE TestData(ID INT IDENTITY (1,1), CreatedDate DATETIME)
GO

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 : http://www.sqlmatters.com/Articles/Top 10 SQL Server Management Studio (SSMS) Tips and Tricks.aspx

Keywords

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


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

Hi,
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.

Regards.

PSF

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

Regards

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 a comment   No login required !

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