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:

1. Script the object using the Object Explorer
2. Query the catalog view – sys.sql_modules
3. 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:

USE AdventureWorks2008R2
GO
--Normal implementation
sp_helptext uspGetBillOfMaterials
GO

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:

1. Navigate out to Tools –> Options
2. Within the Options window, expand the “Environment” node and navigate to the “Keyboard” node
3. Navigate to the “Query shortcuts” node by expanding the “Keyboard” node
4. Notice that various key combinations and assigned stored procedures to be executed are listed in the “Query shortcuts” node
5. 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.

I hope you liked today’s tip!

References:

• Books-On-Line: sys.sql_modules
• Books-On-Line: sp_helptext

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here! | Tweet to @nakulv_sql