SQL SERVER – sp_HelpText for sp_HelpText – Puzzle

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)

Quest

SQL Scripts, SQL Stored Procedure
Previous Post
SQL SERVER – 2005 NorthWind Database or AdventureWorks Database – Samples Databases – Part 2
Next Post
SQL SERVER – 2005 – UDF – User Defined Function to Strip HTML – Parse HTML – No Regular Expression

Related Posts

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

    Reply
    • 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

      Reply
  • Hemal Fofaria
    April 10, 2008 5:57 pm

    Very Good.

    Reply
  • Please let me know how can we find sql script(text of table) of tables from database
    Like’ oralce

    select * from user_source

    Reply
  • What is sp_help in SQL Server?
    I know why this. But I don’t know what is “SP”.

    Reply
  • sp_help is a stored procedure. “SP” MS following the naming standard like that

    Reply
  • RaviShankarKota
    February 1, 2011 2:55 pm

    Thanks for interesting information pinal.

    Reply
  • RaviShankarKota
    February 1, 2011 3:00 pm

    Hi Pinal,

    I have a small doubt.sp_helptext retrieves stored proc definition.But how to get table script using a command ?

    Regards,
    RaviShankar

    Reply
  • 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

    Reply
    • Make sure you are connected to the correct server and also the user that you are using have access to that database

      Reply
  • Is there a form of sp_helptext that can be used on SSRS objects?

    Reply

Leave a Reply