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.
Reference : Pinal Dave (https://blog.sqlauthority.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?