Wednesday, 23 April 2014

SSMS Queries on Function Keys

By David Postlethwaite

At my presentation on SQL Server Management Studio at SQL Saturday in Exeter I promised to write some articles on Gethyn’s blog about the bits I didn’t manage to cover.

If you have simple queries or functions that you use regularly you can assign them to a function key for quick running in a query window

Microsoft have already added a few
If you hit  Ctrl-1 in a query window it runs sp_who
The results are displayed in the results window but the query window doesn’t display the query which can be a bit confusing.

You can set your own up at
Tools ->Options -> Environment -> keyboard -> Query Shortcuts

You’ll see there are already three already listed

Alt-F1    sp_help
Ctrl-1    sp_who
Ctrl-2    sp_lock

The function key Alt-F1 can be very useful.
In a query window type in the name of a system stored procedure such as sp_MSforeachdb
Now highlight the word and hit Alt-F1
The results window will show information on the stored procedure and also the parameters used by the procedure. Very handy.

As well as complete queries you can add partial statements to a function key.
Let me explain
Add the statement  “Select top 100 * from” to Ctrl-4

You must restart SSMS before the function key will work
Now open query Window

Type in the name of a table in the query window.
Now highlight the table name and hit Ctrl-4
The partial query will use the highlighted table name and run the full select query.