3 Additional Tips for Using SQL Server Management Studio

In an earlier post I listed three tips for using the SQL Server Management Studio user interface. I want to offer three additional tips for SSMS.  I think you will find that these ideas will bring additional efficiency and functionality to your use of the interface.

The first tip is related to getting more viewing space after running a query. Query results obviously take up the bottom portion of the SSMS editor window. I have often found myself then clicking in the query editor window and scrolling to view various parts of my query, all the while the results portion of the screen is using a good portion of the window. Some time back an associate told me how to easily address that annoying issue. Simply click CTRL and R to collapse the query results so that the full page is again used for viewing the query text.

The second tip is related to finding objects in Object Explorer. Ever found yourself trying to locate a particular stored procedure that has a name that is a mile long and your brain only remembers part of the procedure name? What about trying to find all the tables related to Orders or Shipping? As it turns out, there is a nice way to search for such things.

Drill down into the object level you are searching through, such as Databases > AdventureWorks2008R2 > Tables. Right click the Tables folder and select Filter. Then select Filter Settings. You are then presented with the dialog below. Enter the name description in the Value column and click OK. This filtering also works for the Views, Stored Procedures and Functions folders.

Filter Search on a Table

 

The third tip relates to using color to visually identify what SQL Server instance the query window is connected to. I once saw a Developer who had five SQL Server Management Studio windows open at once. When another Developer asked him why he answered that it was so he could maintain separate connections to different SQL instances and not be as confused as if he had all the connections in one running instance of SQL Server. It was, I suppose, an admirable attempt to ensure he was running the query against the right SQL instance, but it caused me to remember that color can be used for identifying the SQL instance.

Here is how it works. When SSMS is launched the user is presented with a connection dialog.

SSMS Connect to Server Dialog2

 

Next select Options and the below appears.

 

SSMS Connection Properties Dialog

Click on the Select button, choose a color and then click OK and then Connect in order to connect to the SQL instance. Now open a new query window. The color you just selected will appear along the bottom of the SSMS editor window. To do this after you have already opened SSMS, click Connect > Database Engine > Options from the Object Explorer pane. This type of color customization can also be done with registered servers. For more information on this feature click this link: https://msdn.microsoft.com/en-us/library/hh213617(v=sql.120).aspx

 

I hope these additional tips on SSMS prove useful. Feel free to leave me a comment or another customization that you like to use.

3 Tips for Using SQL Server Management Studio

SQL Server Management Studio, also known simply as SSMS, has many options that can be customized to help you get work done.  In this post I will cover three such options that I have found useful.

The first option discussed will be line numbers. Line numbers can be added to the query editor window to help identify sections of code.  To do this click the Tools menu.  In the drop down then select Options.  Then under Text Editor left click Transact SQL.  Now several options will be available related to what happens inside the text editor screen.  Left click the box next to Line Numbers to enable the display of line numbers next to your code in the editor.

Line Numbers Option in SSMS

 

The second option is query shortcuts. Ever find yourself retyping commands like sp_who2, sp_helpindex from Kimberly Tripp or other handy procedures?  You can actually execute these by assigning them to a keyboard shortcut rather than having to type them each time you want to run the procedure.

To do this, go back to the Tools and Options menu.  Choose Environment, then Keyboard, then Query Shortcuts.  The left column will be labeled Shortcuts.  To the right of the shortcut you wish to use simlpy left click and type the command you want to assign.  Then click OK to assign that shortcut.  Presto!  Now the next time you want to run that favorite metadata proc, just use the assigned shortcut.

SSMS Query Shortcuts

 

The third option is one of my favorite options to adjust. It is the number of recent files and projects that SSMS displays.  This list gives you access to recently opened files and projects.  The default is 10 but I don’t always find that 10 is enough for me.  To adjust this, go to Tools, then Options and Environment.  In the Recent Files section, the number can be adjusted up or down to suit your preference.

 

SSMS Recent Files Option