Customized Keyboard Shortcuts in SSMS

The past few posts have presented some useful code that you might want run readily, at your fingertips.  SQL Server Management Studio (SSMS) can be configured to launch a proc for certain keystrokes.

Go to Tools – Options, then drill down to Environment – Keyboard.  Note that there are some pre-programmed shortcuts which cannot be changed:

Alt+F1   sp_help

Ctrl+1    sp_who

Ctrl+2    sp_lock

In this image, note that I have programmed Ctrl+3.

This proc simply calls out various queries so that can be invoked from the keyboard shortcuts.  I have my keyboard set for these additional shortcuts:

Ctrl+3    vwWho2

Ctrl+4    vwWho3

Ctrl+5    vwWhoActiveSummary

Ctrl+9    vwJobHistory

Ctrl+0    vwJob

After programming the shortcuts, you will have to open a new query window for them to take effect.  You will notice that the Messages tab of the query results pane shows some text that can be copied/pasted into a new window, in case you wish to further refine your query.

Use Admin

go

IF object_id('dbo.KeyboardShortcut') Is Not Null

      DROP PROC KeyboardShortcut

go

CREATE PROC dbo.KeyboardShortcut

      @Code char(5)

AS

/*    DATE        AUTHOR            REMARKS

      9/15/11           PPaiva            Initial creation.

      DESCRIPTION

            Simply selects from a given view.  This is so

                  that a shortcut   can be made to this proc, via SSMC.

      USAGE

            Exec Admin.dbo.KeyboardShortcut 3

            Exec Admin.dbo.KeyboardShortcut 4

            Exec Admin.dbo.KeyboardShortcut 5

            Exec Admin.dbo.KeyboardShortcut 9

            Exec Admin.dbo.KeyboardShortcut 0

*/

SET NOCOUNT ON

IF @Code = '3'    -- Ctrl-3 (shortcut for SSMC)

      SELECT *

      FROM Admin.dbo.vwWho2

      ORDER BY last_batch desc

IF @Code = '4'    -- Ctrl-4

      SELECT *

      FROM Admin.dbo.vwWho3

      ORDER BY last_batch desc

     

IF @Code = '5'    -- Ctrl-5

      SELECT *

      FROM Admin.dbo.vwWhoActiveSummary

      ORDER BY blocked desc, spid

IF @Code In('J', '0')   -- Ctrl-0

      SELECT *

      FROM Admin..vwJob

      ORDER BY NextRun desc, NextRunDateTime

IF @Code In('9')  -- Ctrl-9

      SELECT *

      FROM Admin..vwJobHistory

      ORDER BY RunDateTime desc

IF @Code In ('3', '4', '5')

      Print '

-- Same as Ctrl-3

SELECT *

FROM Admin.dbo.vwWho2

--WHERE LogiName = ''COCREATE\Paul''

--WHERE HostName = ''

--WHERE spid =

ORDER BY last_batch desc

-- Same as Ctrl-4

SELECT *

FROM Admin.dbo.vwWho3

--WHERE LogiName = ''COCREATE\Paul''

--WHERE HostName = ''

--WHERE spid =

ORDER BY last_batch desc

-- Same as Ctrl-5

SELECT *

FROM Admin.dbo.vwWhoActive

--WHERE LogiName = ''COCREATE\Paul''

--WHERE HostName = ''

--WHERE spid =

ORDER BY blocked desc, spid

-- Ctrl 5 (optional sort)

SELECT *

FROM Admin.dbo.vwWhoActive

--WHERE LogiName = ''COCREATE\Paul''

--WHERE HostName = ''

--WHERE spid =

ORDER BY DB, LogiName, Qty desc

'

IF @Code In ('9', '0', 'J')

      Print '-- Ctrl-0

SELECT *

FROM Admin..vwJob

ORDER BY NextRun desc, NextRunDateTime

--ORDER BY Name

-- Ctrl-9

SELECT *

FROM Admin..vwJobHistory

--WHERE Status = ''Failed''

ORDER BY RunDateTime desc

'