Database Drilling Into SQL Server Management Studio

Drilling Into SQL Server Management Studio

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

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

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.

Latest Posts

Related Stories