It was interesting to me. I was using sp_HelpText to see the text of the stored procedure. Stored Procedure were different so I had copied sp_HelpText on my clipboard and was pasting it in Query Editor of Management Studio. In rush I typed twice sp_HelpText and hit F5.
Result was interesting. What are your guesses? My team mates and few of my readers suggested : SQL Server will be in recursive loop, SQL Server will be not responde, SQL Server will throw an error.
Try this:
sp_HelpText sp_HelpText
Result was as expected. SQL Server did its job and displayed the text of sp_HelpText. Nothing unusual happened. This reminded me question I was asked in my high school exam : Write C program, which will print itself. sp_HelpText twice does the same thing. It prints itself.
Getting More Out of sp_HelpText
Beyond the puzzle, this procedure is one of the quickest ways to read the code of a view, function, trigger or stored procedure. A few tips make it nicer to use:
- Switch results to text with Ctrl+T before you run it. In grid mode each line lands in its own cell, which is hard to read.
- Long lines get split, because the procedure returns each line in a column of 255 characters. If a line looks broken, it is probably fine in the real code.
- Objects created
WITH ENCRYPTIONdo not show their text. You get a message that the text is encrypted instead. - You need permission to see the definition. From SQL Server 2005 on, that is the
VIEW DEFINITIONpermission, which owners and administrators already have.
On SQL Server 2005 and later, you can also read the same text with OBJECT_DEFINITION(OBJECT_ID('YourProc')) or from the definition column of sys.sql_modules. Those return the whole code as one value, which is handy when you want to search inside many procedures at once with a LIKE filter.
And the puzzle works because the procedure is itself ordinary T-SQL code stored in the server. Asking it about its own name is no different from asking about any other procedure.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
16 Comments. Leave new
sir,
i need a sample stored procedure for insert , select, alter in a single name or,single procedure.. plz help meee
create procedure test @action varchar(30), @id int, @name varchar(30)
as
begin
set nocount on;
—-insert records into table.
if @action=’insert’
begin
insert into table (id,name)values(@id,@name)
end
—-select records from table.
if @action=’select’
begin
select * from table
end
—update records into table.
if @action=’update’
Begin
update table set col=@col where col=@col
end
—delete record from table
if @action=’delete’
begin
delete from table where col=@col
end
set nocount off
end
Very Good.
Please let me know how can we find sql script(text of table) of tables from database
Like’ oralce
select * from user_source
What is sp_help in SQL Server?
I know why this. But I don’t know what is “SP”.
sp_help is a stored procedure. “SP” MS following the naming standard like that
Thanks for interesting information pinal.
Hi Pinal,
I have a small doubt.sp_helptext retrieves stored proc definition.But how to get table script using a command ?
Regards,
RaviShankar
EXEC sp_help table_name
sp_help table_name gives the details of the table like the column defintions,datatypes, keys,indexes etc.
But what I want is entire definition.It should be like:
Create table Employee
as
Begin
——
——
End
You need to make use of Generate script option from SSMS
Hi Ravi,
did you get the solution for getting the defination of a table?
No Sajal..not of the kind I want..
Regards,
Ravi
Hi Pinal,
When i use sp_helptext i.e fully qualified object name(Ex: ‘Test.GetAllUsers’) i can able to get definition of the stored procedure.But
When i give sp_helptext and just stored procedure name(EX: sp_helptext GetAllUsers ) i am getting following error.
“Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 54
The object ‘GetAllUsers ‘ does not exist in database ‘TempDBLocal’ or is invalid for this operation.”
All this Is all happening in my local machine.
But Sql servers in Dev and production environment in our organization not have this problem. I can get SP’s definition just running sp_helptext GetAllUsers
Please suggest me what changes i need to do in my local sql server 2005.
Thanks
Nagesh
Make sure you are connected to the correct server and also the user that you are using have access to that database
Is there a form of sp_helptext that can be used on SSRS objects?