SSMS Custom Keyboard Shortcuts

There has been some discussion about shortcuts to SQL Server metadata on some of the mailing lists I am on.  I thought I'd share the shortcuts I use on a daily basis with people.  Now for my listing.  I'll include the defaults from SQL Server already.  To get to the shortcut menu, click on Tools...Options...KeyboardYou may need to close out all instances of SSMS and restart it for changes to take effect.

Shortcuts Selected
Objectname
Stored Procedure
Alt+F1 Y/N sp_help
Ctrl+F1   -- nothing at this time
Ctrl+1 Y/N sp_who
Ctrl+2 Y/N sp_lock
Ctrl+3 Y/N sp_who2
Ctrl+4 Y sp_helpindex
Ctrl+5 Y sp_helptext
Ctrl+6 Y/N sp_helprotect
Ctrl+7 N sp_DATABASE_INFORMATION
Ctrl+8   -- nothing at this time
Ctrl+9 Y exec sp_executesql N'select PK.TABLE_SCHEMA as REFERENCED_TABLE_SCHEMA, PK.TABLE_NAME as REFERENCED_TABLE_NAME from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF_CONST inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON REF_CONST.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG AND REF_CONST.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA AND REF_CONST.CONSTRAINT_NAME = FK.CONSTRAINT_NAME AND FK.CONSTRAINT_TYPE = N''FOREIGN KEY'' inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON REF_CONST.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG AND REF_CONST.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA AND REF_CONST.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME AND PK.CONSTRAINT_TYPE IN ( N''PRIMARY KEY'', N''UNIQUE'' ) where FK.TABLE_NAME = @s', N'@s sysname',
Ctrl+0 N declare @id smallint, @sh binary(20), @st nvarchar(4000); declare @tspid table (id int identity(1, 1) not null, spid smallint not null, sql_handle binary(20), sql_text nvarchar(4000)); set @id = 1;insert into @tspid (spid, sql_handle) select spid, [sql_handle] from master.dbo.sysprocesses sp with (nolock) where sp.spid >= 50 and sp.spid <> @@SPID and sp.dbid = db_id();while 1=1 begin select @sh = sql_handle from @tspid where id = @id; if @@rowcount <> 1 break; select @st = [text] from ::fn_get_sql(@sh); update @tspid set sql_text = @st where id = @id; set @id = @id + 1; end; select sp.spid, t.sql_text, sp.cpu, sp.physical_io, sp.blocked, sp.hostname, sp.program_name, sp.status, sp.cmd, convert(sysname, rtrim(sp.loginame)) as loginname from master.dbo.sysprocesses sp (nolock) inner join @tspid t on t.spid = sp.spid where sp.spid >= 50 and sp.spid <> @@SPID and sp.dbid = db_id();

* For Selected Objectname, Y means it must have one, N means it should not, and Y/N means either works.

** The sp_DATABASE_INFORMATION stored procedure comes from here, and I normally put that in my master db.

*** My Ctrl+9 shortcut finds the FK referenced tables for the object you are checking.

Is there anything other shortcut command you like to use?

2 Comments

  1. Julian Says:

    I dont know if you read this, but that is really helpful especially the query at 'Ctrl+9'

    I need this query every day 'SELECT TOP 10 * FROM' :)

  2. David L. Penton Says:

    Well, the format for the CTRL+9 query will certainly help you there :) Glad this is helpful to you.


Leave a Reply