About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Friday, September 16, 2011

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
'


No comments:

Post a Comment