Problem
The SQL Server management tools offer a lot of hidden features, but finding all of these features is sometimes done by accident or by necessity to get a particular job done. One particular feature that would be helpful is to assign shortcuts for particular stored procedures that you run quite frequently. By default SQL Server offers default shortcuts such as Alt+F1 (sp_help), Ctrl+1 (sp_who) and Ctrl+2 (sp_lock). How can you assign your own frequently used stored procedures to shortcut keys?
Solution
In the query tool you have the ability to assign keyboard shortcuts for stored procedures that you run frequently. By default SQL Server assigns some of the most common tasks as such sp_help, sp_who and sp_lock to certain key combinations. But in addition to these 3 predefined keyboard shortcuts SQL Server also offers you the ability to assign some of your own favorite stored procedures.
To access the screen below, open a query window and select from the menu Tools -> Options and then select Keyboard under the Environment node.
How to assign a shortcut key
At this point you just need to enter the stored procedure that you want to assign to the keyboard shortcut. By default SQL Server will follow the same rules for finding the stored procedure as when using the query windows. If the stored procedure is not found it will give you an error stating that the stored procedure could not be found
You can also specify the database where the stored procedure exists such as:
master.dbo.sp_who2 AdventureWorks.HumanResources.uspGetEmployeeList
Here is an example where these two stored procedures have been assigned to a shortcut key. To run these you just need to issue the correct keyboard combination such as Ctrl+3 or Ctrl+4.
Next Steps
- Next time you find yourself running the same stored procedures over and over again think about using keyboard shortcuts
- Take a look at these other query analyzer tips
