Gregg Stark on SQL Server

SQL Server Management Studio Query Shortcuts

I have always hated typing and using the mouse so I always try to find shortcuts and hotkeys for every development environment I am in.  Having said that I thought that I would share what I have custom setup for SQL Server Management Studio.  Until I started playing around with them I really didn't realize what all was possible.  Basically you just highlight some text in query analyzer and then hit the hotkey and it passes the highlighted text into whatever the code is behdind the hotkey.  So here is what I use currently

ALT+ F1 = sp_help - This is useful to highlight a  table or proc and then it will return all the input parameters or columns on the table

CTRL+ F1 = sp_helptext - This will give the compiled version of a proc or function and is much easier than finding it in object explorer.  I usually do a ctrl - t before running this to put the results in text mode.

CTRL+ 1 = sp_who2 - This returns all spids currently active

CTRL+ 2 = sp_block_info - This will show all blocks on the database and is helpful if you are currently experiencing blocking as it shows the statement being blocked and the blocking statement.  This is a custom proc taken from the Microsoft SQL Server Development Customer Advisory Team Blog.  Here is the code for it.  NOTE: this only works on databases running SQL Server 2005 as it uses Dynamic Management Views.

CREATE proc sp_block_info
as
select getdate() as BlockDate,
      db_name(resource_database_id) as [database],
      t1.resource_associated_entity_id as [blk object],
      t1.resource_type as [lock type],
      t1.request_mode as [lock req],   --- lock requested
      t1.request_session_id as [waiter sid], --- spid of waiter
      t2.wait_duration_ms as [wait time],         
      (select text from sys.dm_exec_requests as r  --- get sql for waiter
        cross apply sys.dm_exec_sql_text(r.sql_handle) 
        where r.session_id = t1.request_session_id) as waiter_batch,
      (select substring(qt.text,r.statement_start_offset/2, 
        (case when r.statement_end_offset = -1 
        then len(convert(nvarchar(max), qt.text)) * 2 
        else r.statement_end_offset end - r.statement_start_offset)/2) 
        from sys.dm_exec_requests as r
        cross apply sys.dm_exec_sql_text(r.sql_handle) as qt
          where r.session_id = t1.request_session_id) as waiter_stmt,    --- statement blocked
             t2.blocking_session_id as [blocker sid], -- spid of blocker 
        (select text from sys.sysprocesses as p --- get sql for blocker
          cross apply sys.dm_exec_sql_text(p.sql_handle) 
          where p.spid = t2.blocking_session_id) as blocker_stmt
      from 
      sys.dm_tran_locks as t1, 
      sys.dm_os_waiting_tasks as t2
      where 
      t1.lock_owner_address = t2.resource_address

CTRL+ 3 = sp_findtext - This is a custom proc I have that searches all procs, functions, views for whatever you have highlighted.  This is great for finding all the places a column is used or a table is referenced.  Here is the code for it.  Let me know if you have anything different that works better.

CREATE  procedure sp_FindText
@in_text varchar(8000)
as

-- exec sqldev..sp_findtext 'customer'

set @in_text = '%' + @in_text + '%'

select [Database], [Name], [Type]
from (
    select       'mydb' as [Database], o.Name, o.Type
    from        mydb..syscomments c (nolock)
    join         mydb..sysobjects o  (nolock) on o.id = c.id
    where     c.text like @in_text
    union
    select       'mydb2' as [Database], o.Name, o.Type
    from        mydb2..syscomments c (nolock)
    join         mydb2..sysobjects o  (nolock) on o.id = c.id
    where     c.text like @in_text
) x

order by [Database], name, Type

ctrl + 4 = select top 100 * from - This is one of my favorites....no more typing select * from mytable to see what the data looks like for a given table.  Simply highlight the tablename in the query window and hit ctrl + 4 and you will get the first 100 rows.  Handy to say the least!!

The rest of the ones I have setup do a select from specific tables.  For example say you have a customer table and you always need to lookup a customer by customer number when doing ad hoc queries in query analyzer.  You can setup a hotkey for something like this "select * from Customer where CustomerNumber = ".  Then simply type a customer number in query analyzer (say 123456), highlight it and hit the hotkey for it.  I have 4 or 5 hotkeys setup like this and it really saves a lot of typing and increases productivity quite a bit. 

Also here is a link to all of the editor hotkeys for SQL Server Management Studio. 

http://msdn2.microsoft.com/en-us/library/ms174205.aspx

Lastly, if anyone has any other hotkeys setup that are different than what I have, please post a comment.