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.