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.

4 Replies to “3 Additional Tips for Using SQL Server Management Studio”

  1. I don’t do this kind of work, Lee, but if I did, your instructions are certainly clear and easy to follow. By the time I finished reading through it, I felt like I could do it. You’re a good writer Lee!

    Best regards, “Dee” Elaine Wood-Lane

Leave a Reply

Your email address will not be published. Required fields are marked *