Chapter 19. Extending your productivity in SSMS and Query Analyzer

Pawel Potasinski

Many SQL Server developers and administrators prefer to use T-SQL code instead of a graphical interface to perform their common duties. I’m definitely one of those T-SQL maniacs. That’s why the ability to define custom keyboard shortcuts in SQL Server Management Studio (SSMS) in Microsoft SQL Server 2005 and 2008, and SQL Server Query Analyzer in Microsoft SQL Server 2000, is one of my favorite features of those applications. I love the idea that during the development of my database, which contains more than 100,000 objects at the moment, I can use my utils to easily perform everyday tasks such as searching for a specific object, showing the object definition, or finding dependencies between database objects. If you spend some time on writing your own utils to fit your needs, I can promise you won’t regret it.

Custom keyboard shortcuts

Both Query Analyzer and SSMS provide the ability to call T-SQL code with custom keyboard shortcuts. You can define the shortcuts using the main menus of those applications (note that the way you define the shortcuts in both applications is slightly different).

To define custom shortcuts in Query Analyzer:

1. On the Tools menu, click Customize... (see figure 1).

   Figure 1. To define custom keyboard shortcuts in Query Analyzer, in the Tools menu, click Customize...

   

   • In the Customize window (shown in figure 2), add your T-SQL code next to the chosen keyboard shortcut and click OK.

     Figure 2. Keyboard shortcuts defined in the Customize window in Query Analyzer

     

To define custom shortcuts in SSMS:

1. On the Tools menu, click Options... (see figure 3).

   Figure 3. To define custom keyboard shortcuts in SSMS, in the Tools menu, click Options...

   

2. In the Options window, in the Environment node, click Keyboard (see figure 4).

   Figure 4. Keyboard shortcuts defined in the Options window in SSMS

   

3. Add your T-SQL code next to the chosen keyboard shortcut and click OK.

In both applications, some shortcuts are reserved by default for system stored procedures—for example, sp_who and sp_help.

What makes this feature powerful is that you can use these shortcuts with the text selected in the query editor window. You select the text, press the appropriate shortcut, and then the code assigned to the shortcut is concatenated with the text you’ve selected and the result of concatenation is executed. Let’s see some examples. By default, the Alt-F1 shortcut is reserved for the sp_help system stored procedure. Open a new query window in SSMS (or Query Analyzer), connect to any SQL Server 2005 or 2008 instance, and type the following:

'sys.objects'

Then select the text you’ve just written and press Alt-F1. This should display the result of the sp_help procedure executed with 'sys.objects' as a parameter (the result should consist of the metadata of the sys.objects catalog view).

---

Note

In Query Analyzer, custom keyboard shortcuts also work with text selected in the Results or the Messages tabs. In SSMS, you can’t use custom shortcuts with text selected in any of the mentioned tabs.

---

Creating your custom utility to use with keyboard shortcuts

When you know how to define your own keyboard shortcuts, you can create some custom stored procedures to use with the feature. I call those procedures utilities or—shorter—utils, probably because of their frequent use in my everyday work. Most of my utils are procedures to query the metadata and return some information needed for writing some new T-SQL code. Let’s create a sample util to demonstrate the idea.

How often do you need to get the list of a table’s columns or procedure’s parameters? In my experience, this needs to be done on a regular basis. Let’s write a utility named sp_getcolumns, which will return a list of columns or parameters of a database object of our choice.

---

Note

The sample stored procedure sp_getcolumns demonstrated in this chapter will work in SQL Server 2005 and 2008.

---

The prefix sp_ isn’t accidental here. If you want the procedure to work with database objects in every single database in your SQL Server instance, the best way is to create it in the master database and name it with an sp_ prefix so that you can easily call it no matter what the current database of your session is (SQL Server will search the master database for objects prefixed with sp_).

Listing 1 shows an example of what the procedure’s code can look like. You can add some improvements. I’ll provide some suggestions later in this chapter.

Listing 1. Creating sample utility sp_getcolumns

USE  master
GO

IF OBJECT_ID('dbo.sp_getcolumns','P') IS NOT NULL
  DROP PROC dbo.sp_getcolumns
GO

CREATE PROC [dbo].[sp_getcolumns]
@object sysname,
@horizontal tinyint = 0
AS
SET NOCOUNT ON

DECLARE @lines TABLE (
  line_id int identity(1,1) primary key,
  line nvarchar(4000)
)

IF EXISTS (SELECT 1 FROM sys.all_columns WHERE [object_id] = OBJECT_ID(@object))
BEGIN
  IF @horizontal = 1 BEGIN
    DECLARE @line nvarchar(4000)
    SET @line = N''
    SELECT @line = @line + [name] + N', '
    FROM sys.all_columns
    WHERE [object_id] = OBJECT_ID(@object)
    ORDER BY column_id
    INSERT @lines (line)
    SELECT LEFT(@line,LEN(@line)-1)
  END
  ELSE BEGIN
    INSERT @lines (line)
    SELECT [name] + N','
    FROM sys.all_columns
    WHERE [object_id] = OBJECT_ID(@object)
    ORDER BY column_id
    UPDATE @lines
    SET line = LEFT(line,LEN(line)-1)
    WHERE line_id = @@IDENTITY
  END
END
SELECT line AS ' ' FROM @lines ORDER BY line_id
GO

First of all, note that I use the sys.all_columns catalog view to retrieve the column list (to retrieve the column list in SQL Server 2000, you should use the dbo.syscolumn system table).

---

Note

Normally you should avoid the sp_ prefix for your stored procedures. Use it only in development or for testing, and not in your production databases. Also, it may be required to mark your newly created procedure as a system object with an undocumented stored procedure—sp_MS_MarkSystemObject. Otherwise your procedure may not work properly with all databases within the SQL Server instance. Remember that the sp_MS_MarkSystemObject system procedure is for internal use only and isn’t supported by Microsoft; therefore, never use it against objects in your production databases.

---

The procedure has two parameters: @object (the name of the database object for which the column list should be returned) and @horizontal—this parameter decides whether the columns are returned as a single line (@horizontal = 1) or each column is returned as a single line in a result set (@horizontal <> 1).

The SET NOCOUNT ON line should be used as a best practice in every stored procedure you write to limit the information messages (such as the number of rows affected by the query) sent by SQL Server to the applications.

My procedure uses a table variable called @lines. A simple IF condition controls the way the column/parameter list is inserted into the @lines variable. I wanted the result list to be comma separated; therefore, the comma is concatenated to every column/parameter name. The last comma is unnecessary, so I remove it, either by using LEFT function (for column/parameter list returned horizontally) or by simple UPDATE statement (for the list returned vertically). Finally, all rows from the @lines table variable are returned in the appropriate order. Simple, isn’t it?

All you have to do after you create the procedure is to assign a custom keyboard shortcut to it. Then you can test the util. Go to one of your databases, write the name of one of your database objects (if you use the fully qualified name of the object, put it in single quotes), and press the appropriate keys on your keyboard. The example is shown in figure 5. A parameter list of the sp_getcolumns stored procedure is returned (current database: master).

Figure 1. Sample use of the sp_getcolumns utility

Some thoughts on how you can improve the procedure presented in this chapter:

• Add an option to return the data types of the columns/parameters.
• Use nvarchar(max) data type for line column in the @lines table variable and for the @line variable (in SQL Server 2005/2008 only).
• Perform concatenation with FOR XML PATH clause (in SQL Server 2005/2008 only) or CLR aggregate function (this will let you avoid the nasty string concatenation performed on the @line variable).
• Use the NOLOCK hint to avoid unnecessary locking on the system objects.

Some ideas for utilities to implement

Here are some ideas of other utilities that might be useful for you:

• Searching for database objects by name
• Searching for database objects depending on the given object
• Scripting database objects
• Selecting sample rows from a table

This is a call to action! Create your own utilities, assign them to the custom shortcuts, and make your everyday work more efficient than ever before.

Summary

With custom keyboard shortcuts and your own stored procedures, you can immensely improve your productivity. A skilled developer can avoid using the mouse in a graphical interface and just use a keyboard to execute T-SQL code. As a result, some SQL Server developers are considered magicians because they can do everything just by quickly tapping the keyboard.

About the author

Pawel Potasinski is a database developer and consultant working for Asseco Business Solutions S.A. corporation. He’s been working with SQL Server since 2000. His focuses are data transfer processes, performance troubleshooting, and dynamic code generation. Pawel holds MCT certification since 2004 and is a SQL Server MVP since 2008. In 2007, he founded the Polish SQL Server User Group.