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:
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:
- 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.
I hope you liked today’s tip!
- 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
How to set the same settings in SQL Server 2012. UI is little bit confusing.
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.
Thank you Nakul,
It’s my mistake. Now i got it.
In SQL Server 2012, Under Keyboard Menu, there are 2 sub menus. I have not seen these sub menus. But in SQL Server 2008, we have only Keyboard menu.
@ramkoti: You are welcome!