SQL Server Management Studio (SSMS) has been around since SQL Server 2005 and is a surprisingly nice environment from which to manage SQL Server. Of course it has its shortcomings and there are 3d party tools that supply either replacements or add-ins to it. However, if you have not given it a through look over, it might be easy to ignore some of its best features, so I thought I would take you on a quick tour. Everything I list here is for the version of SSMS that shipped with SQL 2012, but most of the items mentioned are available in any version. So, let’s start with some basics and then move on to some more sophisticated things.
SSMS Settings
From the Tools menu, click Options. You now have a wealth of ways to customized SSMS at your fingertips. Too many to go over in detail, so a few of my favorites, then. Environment \ Fonts and Colors gives old geeks like me the chance to adjust the font size for query windows to something I can actually see without straining. (Yes I could go get new glasses, but where’s the fun in that?) But it is not just the Query Window you can adjust here, take a second and drop down the list of windows that you can adjust fonts for. There is also a zoom factor for the Query Window and for the Messages section of the Results Window. In addition to font size you can adjust all the things you would expect (font, color etc.).
In Tools \ Options \ Startup you can choose the default layout for when you first start SSMS.
In Tools \ Options \ Text Editor \ Transact-SQL you can change the size of tabs as well as control IntelliSense behavior.
You can start SSMS from the command prompt, and while I realize that it is no longer fashionable to start programs that way, there can be some advantages. You can use this to start SSMS such that it is focused on a particular server or database, to use specified login information and / or to automatically open a file(s). To see the details, at a command prompt, type SSMS /?
“SSMS –S MIA-SQL1 –d AdventureWorks2008R2” opens up the specified server and a query window focused on that database without bringing up the typical Connection dialog box.
Projects
Probably you are already aware of SSMS’ ability to create Projects, but just in case you don’t I will explain. Of first importance here is viewing the Solutions Window. If you can’t see it, press Ctrl+Alt+L, or choose it from the Windows menu in SSMS. Now you’re ready to go, so click File / New / Project. Give your new project a name and define where you want it to be stored (Hint: the default My Documents is not a good choice.) If you have Team Foundation Server or Visual Source Safe or some such, there will be a checkbox to check this project into your document management system. In the Solutions Explorer, you will notice that you have a Solution with a Project in it. While not very practical for most of the stuff we do, you could have multiple projects that are saved together. We’ll just stick to a 1 Solution : 1 Project ratio. Within the project, you will see folders for Connections, Queries and Miscellaneous files.
Start by creating a connection. As you might guess, Right-Click and click New Connection and you will see the familiar Connection dialog box. Fill it out and you’re done. Next, Right-Click Queries and choose your connection. You will want to rename the query, but be sure to leave the .sql extension on the file name. Add more queries and connections as you need them. Miscellaneous folder holds whatever you want. I normally use it to include support documents like OneNote files describing interviews I did with Line of Business owners, design docs etc. Of course that may not apply if the project is a smaller one. Lastly, don’t forget that in Object Explorer, you can Right-Click most objects and script them as Create statements and then paste that script into a query file in your project. This can be a great way to make sure that your objects can be re-created even if you lose everything, just save your project to a safe location.
You can save the individual query files one at a time, but if you Save All it will save not only the queries but the entire project as well. Next time you want to open the project you are working on, just click File / Recent Projects and Solutions and voila!
Object Explorer
Object Explorer can do lots more than you may think. In addition to the ability to script objects, as noted above, there is a fairly extensive ability to script a database. Right-Click a database and you will see the regular Script Database as… link, but ignore that and click Tasks. Under Tasks, click Generate Scripts; it will start a wizard that will let you choose to script all of the database objects, or to select all objects of a certain kind, or to select individual objects for scripting. Not only will this provide a nice set of scripts for you to run to recreate the DB and all/some of its objects, it also provides a great way for you to start learning a database to which you are new.
We all know that it is unwise to write SELECT * FROM… kinds of queries, but if you are a bad and lazy typist like I am, typing out the column names is agonizing. Instead, write the SELECT part and then expand the table in Object Explorer and drag the Columns folder just past the SELECT and it will list all the columns for you automatically. In a similar vein, if you want only a few of the columns, you can make better use of Intellisense by typing Select FROM tablename. Then you can go back and start listing your columns, IntelliSense can now kick in and help you with the names.
While you’re in Object Explorer, Right-Click on a table and click Dependencies. This will show you a list of items that this table is dependent upon as well as a list of items that are dependent upon this table. Very useful information when you need to modify a table or its contents. While you are there, Right-Click the table again and look at the Properties of the table, more valuable information is found there such as storage and partition information, permissions, change tracking, collations, etc.
Object Explorer Details
I don’t hear much about Object Explorer Details, but I ran across this great article by Andy Hogg: https://www.computerweekly.com/tip/7-secrets-of-SQL-Server-Management-Studio
They say that the devil is in the details, but there is a wealth of information there as well. Simply click on a folder in Object Explorer, say Tables under a database. While that folder is selected, on the menu bar click View / Object Explorer Details or just press F7. At first glance, your thoughts may run to the “So what?” range, but let’s look deeper. First, take a look at the columns of information displayed about each table. Like with most UI’s you can Right-Click on the columns and add new ones to the display. Obviously, with the different types of objects, the column list will vary.
In Object Explorer, you cannot select more than one item at a time, but in Object Explorer Details you can. So, if you wanted to drop multiple objects, or script them you can do so without repeating the operation for each individual item.
The Query Window
The Query Window is probably as familiar to you as any of the windows in SSMS, but there may be a few surprises left in it. First, take a look at the Outlining feature. You can toggle it from the menu Edit / Outlining. Sometimes it seems a bit confusing as to what constitutes a block for outlining, but it is basically controlled through Go statements and Begin / End statements; this includes Begin Try / End Try and Begin Catch / End Catch. This can be used to collapse large areas of code that you don’t need or want to look at.
To do the opposite, take advantage of splitting windows via Window / Split if you are looking at two places in the same query file. You may want to see two scripts at the same time. To do this, open those scripts as you normally would. Now, Right-Click on the tab of one of the scripts and select New Horizontal Tab Group or New Vertical Tab Group. To return to the normal view, once again Right-Click the tab and select Move To Next Tab Group.
If you’re like me, you end up with tons of scripts open at once and you want to close them. Simply Right-Click the tab of the one you want to keep open and select Close All But This.
Quite often, when I am teaching, I have many scripts and multiple servers open and when I try to execute a script I find it is focused on the wrong server. To change this, I just Right-Click anywhere in the Script Window and select Connection / Change Connection and the old Connect To Server dialog box appears and I can choose the correct server connection.
While you’re in the Script Window, don’t forget to look at the properties of the session, you will find lots of goodies in there.
Finally, here’s a trick I learned not so long ago from my friend and hero Glen Wooden. I’m sure that you have all used sp_help ‘table_name’ before; if not, you are in for a great surprise. It is one of my favorite sprocs. But, instead of typing all that out, just highlight the name of a table and then press ALT+F1. It runs sp_help using the highlighted table name as its parameter and displays all sorts of valuable information.
You can also add your own keyboard shortcuts. Tools / Options / Environment / Keyboard / Query Shortcuts, although I have had limited luck with getting this set the way I want it.
The Results Window
OK, not many surprises here but there are a couple of interesting tid-bits. I’m often surprised that people don’t know how to get rid of it. CTRL+R toggles the Results Window. If you look at the Messages tab, you can adjust the zoom, but not in the Results tab.
If you query a table with spatial data in it, the Results Window will attempt to draw a map for you. After your query executes, you will see a tab called Spatial Results. This tab will display a borderless map with points for each of your locations. It is limited, but still fun.
You can save the results of your query by Right-Clicking in the Results Window and selecting Save Results As… and you will be able to save them as a .txt or .csv file. This is a nice shortcut if you meant to send results to a file but forgot to click that option.
Bookmarks
When your scripts get long, it is easy to lose your place, or perhaps you find that you keep having to return to those variable declarations or that function definition. Well, you can always bookmark popular spots and return to them. First get to the Bookmarks Window. Either press CTRL+K followed by CTRL+W (don’t ask me how they came up with that keystroke combo) or from the menu system Edit / Bookmarks. Now you can easily move around without interminable scrolling.
Task / Comments Window
At times, I have to stop a project in order to focus on some new demand that needs my attention. Why can’t they leave me in peace? I just got good and focused on this sproc and I can clearly think through that insane join clause and suddenly some table needs to be restored. Can’t that wait until tomorrow? What idiot dropped it anyway? Now when I finally get back to the code I can’t remember what needed to be done or why I had written that code that looks so inscrutable to me now. Plus, I know there was something else I had to do before I actually ran the sproc, what was that? Ever felt like that? I know I have. I have learned that Comments are always for the benefit of the next poor fool to look at my code, and that poor fool is commonly me. However, comments are not the best place to leave To-Do things and extensive comments that will be irrelevant once the code is complete. Hence the Task / Comments Window. Press CTRL+ALT+K, or click View / Task List and your troubles are over. You can store Tasks to be done and comments right there and you will have a neat reminder for when you try to pick up where you left off.
Multiple Servers
You can execute scripts, or evaluate policies against multiple servers at the same time. First you need to create a server group. To do that, open the Registered Servers Window (View / Registered Servers). In that windows Right-Click Central Management Servers and select Register Central Management Server. Pick a server to start with. Next, expand Central Management Servers and Right-Click that server you just defined. Select New Server Group and give the group a name (“Production Sales Servers”) and description. Right-Click the group you just created and select New Server Registration. Repeat that process until you have registered all of the servers you want to be in that group. Now you’re ready for the fun. Right-Click the group and select New Query and a Query Window will appear. Write a query, something like SELECT * FROM sys.databases or whatever, and execute it. Take a look at the Results Window. There is a new column that lists the server name for each record returned followed by the actual results. You can modify what show up here. Tools / Options / Query Results / SQL Server / Multiserver Results. You can also evaluate policies which you have created against the group. You can even import any policies you create to each of the servers. Note: In order to Import a policy to the group, you must first Export the policy to an .xml file.
Ok, that wraps up this bit of fun. I sincerely hope you have learned something worthwhile, or at least something that will make your workload a bit easier.
Questions or comments, please get in touch with me at jeff.rathjen@quickstart.com