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 (http://blog.SQLAuthority.com)

17 thoughts on “SQL SERVER – sp_HelpText for sp_HelpText – Puzzle

    • create procedure test @action varchar(30), @id int, @name varchar(30)
      set nocount on;
      —-insert records into table.
      if @action=’insert’
      insert into table (id,name)values(@id,@name)
      —-select records from table.
      if @action=’select’
      select * from table
      —update records into table.
      if @action=’update’
      update table set col=@col where col=@col
      —delete record from table
      if @action=’delete’
      delete from table where col=@col
      set nocount off


  1. 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.



  2. Pingback: SQL SERVER – Weekly Series – Memory Lane – #033 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s