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

'

vwJobHistory

This view shows the job history, using a udf to nicely format the run date/times.

Examples of columns in msdb.dbo.sysJobHistory in the “unfriendly” format:

run_date             run_time

20110830             162812

The unused columns from the two tables are commented out, so that if you ever have a need to show them it is easy to insert them into the active part of the query.

This view has a dependency on this object which is easy to install:
        udfFormatTimeWithColons

Use Admin

go

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

      DROP VIEW dbo.vwJobHistory

go

CREATE VIEW [dbo].[vwJobHistory]

AS

/*    DATE        AUTHOR            REMARKS

      9/7/11           PPaiva            Initial creation.

     

      SELECT *

      FROM vwJobHistory

      ORDER BY RunDateTime desc

      SELECT *

      FROM vwJobHistory

      WHERE Status <> 'Succeeded'

      ORDER BY RunDateTime desc

*/

SELECT CASE WHEN Run_Status = 0 THEN 'Failed'

                  WHEN Run_Status = 1 THEN 'Succeeded'

                  WHEN Run_Status = 2 THEN 'Retry'

                  WHEN Run_Status = 3 THEN 'Canceled'

                  WHEN Run_Status = 4 THEN 'In Progress'   

                  ELSE 'Undefined Status in View'

            END Status,

            j.Name,

            --Run_Date,

            --Run_Time,

            Convert(varchar(16),

                  CASE WHEN IsNull(Run_Date, 0) = 0 THEN Null

                         ELSE Convert(datetime,

                                          Convert(varchar, Run_Date) +

                                          ' ' + dbo.udfFormatTimeWithColons(Right('000000' + Convert(varchar, Run_Time), 6))

                                                      )

                        END, 120) RunDateTime,

            Convert(decimal(8, 1), run_duration/60.) Mins,

            Run_Duration Secs,

           

            step_id, step_name, server,

            message,

            jh.job_id,

            instance_id

            --sql_message_id, sql_severity, run_status, operator_id_emailed, operator_id_netsent, operator_id_paged, retries_attempted

FROM msdb.dbo.sysJobHistory jh

JOIN msdb.dbo.sysJobs j

            ON j.job_id = jh.job_id

vwJob

This view is helpful for showing all the jobs that exist on this instance of SQL Server.  Note the very handy first column, NextRun which indicates which scheduled jobs are to be run in the future, or which have already run for this day.

If I ever need to shut down a server, or shut down SQL Agent, I query this view first so I know what jobs are scheduled to run next, or what jobs may not run during the downtime.

This view has a dependency on this object which is easy to install:
        udfFormatTimeWithColons

Use Admin

go

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

      DROP VIEW dbo.vwJob

go

CREATE VIEW [dbo].[vwJob]

AS

/*    DATE        AUTHOR            REMARKS

      9/3/11            PPaiva            Initial creation.

     

      DESCRIPTION

            Returns active jobs by next scheduled date (works only on SQL 2005).

      NOTE that since this JOINs to sysJobSchedules, this view

            does not necessarliy show a distinct list of Job Name.

      DEPENDENCIES

            udfFormatTimeWithColons()

      USAGE

            -- The next job to be executed is shown at the top

            SELECT *

            FROM vwJob

            ORDER BY Server, JobEn desc, SchedEn desc, NextRunDateTime

            SELECT *

            FROM vwJob

            ORDER BY Server, JobEn desc, SchedEn desc, OrderByMe, NextRunDateTime

*/

WITH cte (Server, JobEn, SchedEn, NextRunDateTime, Name,

                  Category, SchedName, Next_Run_Date, Next_Run_Time,

                  JobCreated, JobModified, Description, Job_ID)

AS

(

SELECT  Convert(varchar(50), ServerProperty('ServerName')) Server,

            J.Enabled JobEn,

            ss.Enabled SchedEn,

            Convert(varchar(16),

                  CASE WHEN IsNull(JS.Next_Run_Date, 0) = 0 THEN Null

                         ELSE Convert(datetime,

                                          Convert(varchar, JS.Next_Run_Date) +

                                          ' ' + dbo.udfFormatTimeWithColons(Right('000000' + Convert(varchar, JS.Next_Run_Time), 6))

                                                      )

                        END, 120) NextRunDateTime,

            J.Name,

            C.Name Category,

            ss.Name SchedName,

--          Originating_Server OrigServer,

            JS.Next_Run_Date,

            JS.Next_Run_Time,

            J.Date_Created JobCreated,

            J.Date_Modified JobModified,

            J.Description,   

            J.Job_ID

FROM msdb.dbo.sysJobs J

LEFT JOIN msdb.dbo.sysJobSchedules JS

      ON JS.Job_ID = J.Job_ID

LEFT JOIN msdb.dbo.syscategories C

      ON C.Category_ID = J.Category_ID

LEFT JOIN msdb.dbo.sysschedules ss

      ON ss.schedule_ID = js.schedule_ID

)

SELECT CASE WHEN NextRunDateTime Is Null

                        THEN 'Null'

                   WHEN NextRunDateTime < GetDate() - 1

                        THEN 'Past'

                  ELSE 'Future'

            END NextRun,

            *,

            CASE WHEN NextRunDateTime Is Null

                        THEN '2200-01-01'

                   WHEN NextRunDateTime < GetDate() - 1

                        THEN '2100-01-01'

                  ELSE NextRunDateTime

            END OrderByMe

FROM cte

udfFormatTimeWithColons()

This user-defined scalar function is helpful in properly formatting column next_run_time from table msdb.dbo.sysJobSchedules. 

Use Admin

go

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

      DROP FUNCTION dbo.udfFormatTimeWithColons

go

CREATE FUNCTION dbo.udfFormatTimeWithColons(

      @In varchar(6)

      )

RETURNS varchar(8)

AS

/*    DATE        AUTHOR            REMARKS

      9/1/11            PPaiva            Initial creation.

      DESCRIPTION

            Helpful for formatting the next_run_time column

                  in msdb.dbo.sysJobSchedules.

                             

             Input:  073000

            Output:  07:30:00

     

      USAGE

            SELECT dbo.udfFormatTimeWithColons('073000')   

           

      DEBUG

            SELECT *

            FROM msdb.dbo.sysJobSchedules

            SELECT *, dbo.udfFormatTimeWithColons(next_run_time)

            FROM msdb.dbo.sysJobSchedules

           

           

*/

BEGIN

      DECLARE @Out varchar(8)

      SET @Out =        Left(@In, 2)

                        + ':'

                        +     Substring(@In, 3, 2)

                        + ':'

                        +     Right(@In, 2)

      RETURN @Out

END