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...Keyboard. You 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?
There are no comments yet...Kick things off by filling out the form below.