SQL Server tips and experiences dedicated to my twin daughters.

Recently, I was working with a developer colleague of mine to troubleshoot an issue he had with one of his queries. A stored procedure he wrote was consuming data from another procedure and was not functioning as expected. I suspected the issue to be a missing change in the related stored procedure.

The easiest way to verify this is obviously to script out the object and verify for the existence of the required code. Scripting out a programmability object (a stored procedure, function or a view) can be done in any one of the following ways:

Script the object using the Object Explorer

Query the catalog view – sys.sql_modules

Use the sp_helptext system stored procedure

The recommended way is of course, to use the Object Explorer to script out the object. However, the fastest method for most practical purposes is using the system stored procedure sp_helptext.

sp_helptext takes at least one parameter – the object name, with a normal usage similar to the one shown below:

However, there is another way, which is even faster than typing in sp_helptext – which my colleague was surprised to learn about. That method is to customize the keyboard settings in SSMS such that any key one of the various supported, customizable key combinations stands for executing the system stored procedure sp_helptext.

Customizing the SSMS Keyboard

In order to customize the keyboard settings for your SSMS instance, here are the simple steps that need to be followed:

Navigate out to Tools –> Options

Within the Options window, expand the “Environment” node and navigate to the “Keyboard” node

Navigate to the “Query shortcuts” node by expanding the “Keyboard” node

Notice that various key combinations and assigned stored procedures to be executed are listed in the “Query shortcuts” node

Against the preferred key combination, enter the stored procedure that you would like to execute

As you can see from the screenshot above, the following are the default key assignments that come with SSMS:

Alt + F1 = sp_help

Ctrl + 1 = sp_who

Ctrl + 2 = sp_lock

In my case, I had the sp_helptext added as the key combination Ctrl+F1.

Please restart the SSMS after making changes to the keyboard configuration.

Using the customization

Once the keyboard combinations are assigned, there is no longer a need to type in sp_helptext anymore. Simply selecting the object name and using the key combination defined (in my case, Ctrl+F1), executes the stored procedure and results are returned.

The screenshots in this post are from a SQL Server 2012 (CTP) instance. The instructions for the RTM release are also similar. What is the exact confusion that you are having? Maybe that will help me help you out in a better way.