April 19, 2019
Hot Topics:

Drilling Into SQL Server Management Studio

  • February 23, 2006
  • By Mike Gunderloy
  • Send Email »
  • More Articles »

SQL Server 2005 brings many changes, great and small, to the SQL Server developer. One of the largest changes for developers is the replacement of two tools - Enterprise Manager and Query Analyzer - with one, SQL Server Management Studio.

Written using .NET and developed as a ground-up replacement rather than a rewrite, Management Studio can be a jarring experience if you've spent numerous years using the previous round of utilities. But there's a lot to like about Management Studio when you take the time to get to know it. Here are ten things you might not have noticed about the new tool if you're still struggling up the learning curve.

Share Server Registration Information

If you need to use Management Studio with multiple servers on more than one computer, you'll appreciate this shortcut for setting things up quickly. First, register all of your servers (SQL Server, Analysis Servers, and so on) on one instance of Management Studio, and arrange them into groups the way you prefer. Remember, you may need to select Registered Servers from the View window to see the tree of groups and servers above Object Explorer if it's not already visible. Then right-click on any node in the tree and select Export. This will let you create a file containing all of the registration information for that portion of the tree. Take that file to another instance of Management Studio, right-click and select Import. Instant server registrations!

Templates Can Speed Up Your Work

Management Studio includes templates for many common (and not-so-common) SQL tasks. To see them, select Template Explorer from the View menu. For example, you might not create a database across multiple filegroups often enough to have the syntax memorized. No problem! Expand the Database node and double-click the Create Database on Multiple Filegroups template. Supply your connection information, and Management Studio will open the template, complete with placeholders for the parts you should fill in, like the database name and file names.

But that's not the end of templates. Select Specify Values for Template Parameters from the Query menu to open the dialog box shown in Figure 1. Here you can easily fill in all of the placeholders. Click OK and SQL Server will customize the template to your needs; all that remains is to press F5 to run it.

Supplying values for a template

You can also create your own templates. Right-click in Template Explorer and select New, Template. Type a SQL statement and substitute placeholders of the form <parameter_name, data_type, value> wherever you want to be prompted for a parameter. Then select Save from the File menu to save your template.

Summary Page Let You Multiselect

You can only select one object at a time in Object Explorer. But there's a way to select more than one object, as long as all of the objects are the same type: use the summary page, which you can open with the F7 key. For example, click on the Tables node of a database in Object Explorer and press F7. This will open the summary page, showing the name, schema, and creation date for each table in the database. Now you can use Shift-Click and Ctrl-Click to select multiple tables. Right-click on the group of selected tables and you'll find that you can perform a few operations on the group as a whole. For example, you can create a single script that will CREATE or DROP all of the selected tables.

You Can Get Your Old Shortcuts Back

Are you going nuts because Ctrl+O doesn't open a new query window and F2 doesn't go to the next bookmark when you're editing? If your fingers have memorized all of the old Query Analyzer keyboard shortcuts, help is at hand. Select Options from the Tools menu, and Navigate to the Keyboard category of General options as shown in Figure 2. Then select the SQL Server 2000 keyboard scheme to get most of your old shortcuts back, the way you know things were always meant to be.

Selecting SQL Server 2000 keyboard shortcuts
Click here for larger image

Changes Can Be Queued For Later

Management Studio makes it easy to save design changes to objects so that you can commit them later, even though you're working during peak hours. For example, you might need to make some changes to the permissions for a table, but not want those permissions to take effect until after working hours. Just open the Table Properties dialog box and make the changes, but don't click OK. Instead, click the dropdown arrow next to the Script button at the top of the dialog box, as shown in Figure 3, and select Script Changes to Job. This will open the Job Properties dialog box, already populated with the proper SQL step to make your desired changes. This facility is available from most of Management Studio's dialog boxes.

Saving changes for later
Click here for larger image

Help in a Tab

By default, Management Studio's context-sensitive help opens a full-blown instance of Books Online as a separate window. This is great if you have dual monitors; you can drag the Books Online instance to your other monitor, and it still stays synchronized with Management Studio events. But if you've only got a single monitor and want to minimize switching between applications, you'll probably find help more convenient if it's directly in the Management studio shell. To get it there, select Options from the Tools menu and navigate to the Help category of General options. Select the Integrated Help Viewer and click OK.

Operating System Commands Can Be Executed

Query windows in Management Studio default to running T-SQL, but you can also use them to run SQLCMD scripts. One interesting side effect of this is that you can use a query window to execute operating system commands. To do this, click the SQLCMD Mode toolbar button (it looks like a query window with an exclamation point on it) and then type any command that you could enter at a command prompt preceded by two exclamation points. Press F5 or click the Execute Query button as usual to execute the command. Any output will come back in the query results window, as shown in Figure 4.

Using a query window as a command prompt

Stuck? Ask the Community

Management Studio has a direct connection to the MSDN Community Forums. To use it, select Ask a Question from the Community menu. This will open up the Search Community Forums home page in your help interface of choice (either the external Books Online help viewer or the integrated help viewer). Enter some keywords that characterize your question and click Go to see if it's already been answered. If your question isn't already in the forums, you can post it yourself, and check for answers later by selecting Check Question Status from the Community Menu. The communities are surprisingly active; for example, right now a search for questions about "SQL Server Management Studio" turns up over 1200 questions.

Object Explorer Can Be Filtered to Avoid Overload

If you've got a lot of databases with many objects loaded, it can be a real chore to find what you're looking for in Object Explorer. That's what the filter tool is designed to help you with. Click the Filter icon in the Object Explorer toolbar and you can include or exclude objects from view based on their name, schema, or creation date. The filter only applies to the currently-selected node (so, for example, you can filter tables without filtering views). Click the button again to remove the filter.

IntelliSense for T-SQL

My last tip isn't so much about what's in Management Studio as in what was left out of it: IntelliSense. Oh, sure, Microsoft managed to build in IntelliSense for some of the languages that Management Studio supports, but it's still missing for T-SQL, which is probably where you spend most of your time. To remedy this oversight, grab yourself a copy of PrompSQL, which neatly fills the gap. You can download a free evaluation copy, and if you like it registration is only $25.00.

About the Author

Mike Gunderloy is the author of over 20 books and numerous articles on development topics, and the Senior Technology Partner for Adaptive Strategy, a Washington State consulting firm. When he's not writing code, Mike putters in the garden on his farm in eastern Washington state.

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date