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.
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)
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)
set nocount on;
—-insert records into table.
insert into table (id,name)values(@id,@name)
—-select records from table.
select * from table
—update records into table.
update table set col=@col where col=@col
—delete record from table
delete from table where col=@col
set nocount off
Please let me know how can we find sql script(text of table) of tables from database
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.
I have a small doubt.sp_helptext retrieves stored proc definition.But how to get table script using a command ?
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
You need to make use of Generate script option from SSMS
did you get the solution for getting the defination of a table?
No Sajal..not of the kind I want..
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.
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?