SQL Server Management Studio (SSMS) comes with the ability to create shortcut keys to execute Transact-SQL code (T-SQL). This is an often overlooked feature that can be helpful to Database Administrators, Developers, Reporting Analysts, and just about any end user that accesses the database with the SSMS utility. If there are T-SQL stored procedures and possibly queries that are executed on a frequent basis, then the shortcut keys just may be a new found friend.
Did you know that there are already some shortcut keys configured that execute stored procedures with the SSMS installation? There are three that are permanently configured. These have been around for many versions of SQL Server. Here they are:
- Alt+F1 - Executes the sp_help stored procedure.
- Ctrl+1 - Executes the sp_who stored procedure.
- Ctrl+2 - Executes the sp_lock stored procedure.
Not sure if there is anything truly useful with these stored procedure calls aligned with the shortcut keys. Wish Microsoft would allow for these to be removed; unfortunately they do not. There is a limit of nine available shortcut keys that can be configured. If you are using this feature don't be surprised if you'll need more options. For now, if you open up a query window and hit Ctrl+1 the sp_who stored procedure will run. Nice!
Imagine being a Database Administrator with many SQL Server instances to manage. You may want to be able to quickly and consistently check which database files are ready to grow, who is logged in, which jobs are currently running, etc. Or maybe you log onto a specific database and keep running the same set of queries. Why not give the shortcuts a try?
First, create a stored procedure in the master database. In this example we are going to access one of the dynamic management views to gain information about the Windows Services for SQL Server. Wouldn't it be nice to quickly gain information about the services just by performing a Ctrl+3 on any instance you are logged on to? If it is an Enterprise standard to have a stored procedure to check the state/statuses on every instance then this could be beneficial. The sys.dm_server_services view has some good information. Granted, it isn't going to be very helpful if the database engine is not up and running.
Note: To execute the dynamic management view the user will at a minimum need the GRANT VIEW SERVER STATE permission. Because this stored procedure has the sp_ naming convention it should be created in the master database. I'll get into this naming convention at a later time. For now, the sp_ convention indicates this is a global stored procedure that can be run while logged into any database.
After the stored procedure is created go to SSMS. Click Tools->Options->Environment->Keyboard->Query Shortcuts. You should have a dialog box similar to the one shown below.
In the Stored Procedure column next to Ctrl+3, enter the name of the stored procedure sp_ServiceState. Once that is done click OK. The new shortcut will not work with the current connection. Open up a new query window and hit Ctrl+3. Now you have quick and easy access to information about the services. The stored procedure should have executed giving similar results.
Fairly easy and straight forward to implement. This is one of those features a lot of people do not use and I have absolutely relied on it. Let me share some examples of what I have used these for:
- SQL Agent job schedules.
- Information about the processes that are currently running along their wait types and wait times.
- A plethora of information about the filegroups and files on all databases.
- Detailed and aggregated data about the current locks.
- Instance and database level permissions. Great for standardizing audits. PCI and HIPAA complaint? No need to confuse the auditors with ad hoc requests. Give them the same format every time.
- Batch job processing information.
- Table and index statistics for the current database.
- Service Broker Queues
- Replication and Log Shipping states
There is a lot of opportunity here.