Welcome to SqlAdvice Sign in | Join | Help

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. 

Published Friday, August 03, 2007 2:32 PM by gstark

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

Friday, August 03, 2007 2:48 PM by ssmith

# re: SQL Server Management Studio Query Shortcuts

Nice!

Sunday, September 16, 2007 4:55 PM by 窃听器

# re: SQL Server Management Studio Query Shortcuts

Good article, the author thanks!

Wednesday, October 10, 2007 9:49 AM by Gregg Stark on SQL Server

# Retrieve Index Details and Columns in Sql Server 2005

I frequently need to look at what indexes are on a table and I am really not a fan of using object explorer

Thursday, December 06, 2007 7:33 PM by Sankar

# re: SQL Server Management Studio Query Shortcuts

I have been using shortcuts for a while and one thing I haven't successfully able to do (in both SQL 2K & SQL 2K5) is your point 4. Did you actually tried point 4 and if it works for you, can you let me know in more detail to get that working. For me I always get 'incorrect syntax near from' error.

Thursday, December 06, 2007 7:53 PM by Sankar

# re: SQL Server Management Studio Query Shortcuts

I meant ctrl + 4 = select top 100 * from

Monday, December 31, 2007 12:04 PM by gstark

# re: SQL Server Management Studio Query Shortcuts

You need to have sp1 installed for it to work.  It does work for me, but I have seen it blow up for others that don't have sp1 or sp2 installed.

Tuesday, January 29, 2008 8:36 PM by Jared Ko

# re: SQL Server Management Studio Query Shortcuts

I've been using this for a while but this is the first time I've seen the CTRL+4 demo that will allow you to do this without a stored procedure. Thanks!

sp_findtext should be updated to use sys.sql_modules. This uses nvarchar(max) so you won't have a duplication of procedure names if the search text is used multiple times in the definition.

This will also be more accurate in case your search text goes from one entry in syscomments to the next.

Friday, February 01, 2008 11:18 AM by hommer

# re: SQL Server Management Studio Query Shortcuts

Great stuff!

Two things I need some body to help me out.

1) Where did you create this sp_FindText? Master or each user db?

2) When you added "Select Top 100 * From", did you leave the double quote out? Either way, I couldn't make it to work.

BTW, my environment is virtual(VMWare/Citrix type). So, I couldn't use Ctrl_F1 for sp_HelpText because that has been assigned. However, I mapped sp_helptext to Ctrl_3, and it worked.

Thanks!

Wednesday, February 13, 2008 11:29 AM by gstark

# re: SQL Server Management Studio Query Shortcuts

1) I create sp_findtext in the master database.  If you create a stored procedure in the master database and prefix it with sp you can use it in any database.  

2) I don't have any quotes on the select top 100 * from.  What is the error that you are getting?  Do you have at least SP1 installed on your client tools?

Leave a Comment

(required) 
required 
(required) 
Enter the code you see below