This is quick but interesting trick to display the text of Stored Procedure in the result window. Open SQL Query Analyzer >> Tools >> Customize >> Custom Tab
type sp_helptext against Ctrl+3 (or shortcut key of your choice)
Press CTRL + T to enable the text view in result window.
Type any Stored Procedure name in your database, select the SP name and type Ctrl+4.
You will see the Stored Procedure text in the Result Window.
As SQL Server 2005 and onwards version of SQL Server now uses Management Studio instead of query analyzer.
Here are additional blog posts related to the this blog:
- SQL SERVER – SP to display code (text) of SP, Trigger, View or Object
- SQL SERVER – Simple Use of Cursor to Print All SP of Database
- SQL SERVER – sp_HelpText for sp_HelpText – Puzzle
- Fastest Way to Display Code of Any SP – Interview Question of the Week #094
Let us see the fastest way to display code of the SP.
First, go to SQL Server Management Studio and select option Results to Text (shortcut CTRL+T).
Next type following script in Query Editor and hit execute. Do not forget to replace the string ‘NameofYourSP’ with the name of your actual stored procedure.
Reference: Pinal Dave (http://www.SQLAuthority.com)
34 Comments. Leave new
Nice trick for shortcut of sp_helptext. :)
Is there any trick to do this in SQl Server Management Studio ??
There’s something to define shortcuts but it doesn’t work !!
I think we have to add something to indicate the selected text is the parameter of sp_helptext… but don’t know what !!
Thanks
In answer to Hollywood, the shortcuts thing (Tools | Options | Environment | Keyboard) does work, you just have to restart Management Studio before it recognizes the shortcut. It then takes the selected text as the parameter automatically
Good answer..dude it works …
This is useful, but it’s something I am aware of. Call me lazy, but what I am looking for is a way to output the text (code) of a stored procedure in Text mode (results to text) in the Management Studio of SQL Server 2005. I can currently do this by a combination of Ctrl+T and Ctrl+F1 (which I’ve assigned to sp_helptext). Is there any way to achieve both actions using a single keyboard shortcut?
Hi
This is prabu really am enjoying your reply its simply good and i have a issue
This is am trying to use query shortcut i assigned the query for the short cut actually what i did is
1. Option -> Enviroinment -> Keyoard and then i set
ctrl +3 =Select * from
2.Then i come back to query window and selected a table and then i hit ctrl +3 but it dosen’t show anything if you help me for this its really usefull for me
thanks in advance
praburaj
Praburaj, you have to close and re-open management studio for it to “take”.
Hi,
I’m using sql 2008. ctrl+ F is not working.
even selecting find option from the edit menu is not working.
if i select the ctrl+tab it shows the active window for find option , but selecting it doesnt show the find window.
once i closed my sql my short cut’s are deleted ,
i need to keep this ,,
is this possible
I’m using QA2000 and I set the procedure sp_helptext on Ctrl+3 shortcut, for example, and on editor pane I selected (highlighted) a procedure name (salesbycategory on Northwind database) and pushed Ctrl+3, but I got the following error:
Server: Msg 201, Level 16, State 3, Procedure sp_helptext, Line 0
Procedure ‘sp_helptext’ expects parameter ‘@objname’, which was not supplied.
How can I use a shortcut (sp_helptext) and set an argument (salesbycategory) for it?
Thanks
keyboards>> ctrl+3 = sp_helptext
If you need to see the procedure means just give the procedurename
EX:
cur_proc
select above procedure and press ctrl+3 it will work perfect…
ya its really perfect thank you..
Hi
I need to set Select * from to ctrl+3
I have select the tablename by using ctrl+3
it shows
Incorrect syntax near ‘From’. this issue…
Help me…
Thanks…
Give space after from ‘SELECT * FROM ‘
I already have space after from but it still complains of incorrect syntax.
This used to work for me with SQL Server 2005 + Windows XP; and I was recently upgraded to Windows 7.
Any advice, please? :)
This fixes the issue! :)
Please select the text and press ctrl + 3
Hi, I have the same problem with management studio 2005. I have ctrl+3 shortcut assigned to “SELECT * FROM “. I open new query window run shortcut and I get the message Incorrect syntax near ‘FROM’.
It looks like the shortcut doesn’t pass the selected value.
Someone, Please help!!!
Are you running it againt a table name?
You need to highlight the table name and use shortcut ctrl+3
it used to work for me in xp, no longer working in windows 7, anyone knows why? :(
Did you try closing and opening the query analyser?
Is there any short cut key for comment and uncommment the line.
thanks in advance
Zakir
Hi,
Could you tell me, how can i know the SQL server functions like sp_helptext?
Thanks,
A.Prabu
Windows 7 with keyboard short cut for SQL server 2005 It does not work any reference for that
Thank You
Kirti Darji
what is short cut for Funtion dispaly in text
How to create shortcut for procedure which takes more than one argument?
Do you mean you want to skip passing values for some parameters?
yes this can be a case but I want to create shortcut for a procedure which takes more than one parameter. suppose I have a procedure which returns records based on two filter conditions and user has to pass that two values.
e.g.
exec myProc @parm1=value1, @parm2=value2
One more thing how to create shortcut for followinw query
select top 10 * from where RowStatus=1
where RowStatus is
Hi, i tried with the shortcut and i worked bu on problem – it is working for procedures (like sp_help) but not working for procs which are under a schema, any help
Unfortunately for schema objects you have to surround the fully qualified name with tick marks, select all of that and then hit your hotkey: ‘schema.myObject’ <<–like so.
Tools >>Options>> Environment>>KeyBoard
set the specific key to shortcut then easy to get the data or insert or update with out writing all the syantax
Thanks a lot
good one karthik.