sp_helptext caching dropped SP

• sqlcool

  SSC Eights!

  Points: 967

  More actions

  December 7, 2010 at 9:42 pm

  #141579

  Hi there

  I have dropped a SP and created and our app was still using the old SP text so i went to SQL-server (2005) and used

  SP_helptext to see the text of the SP it was still not updated. I went to object explorer the SP was updated to new 1

  I then used sp_recompile to recompile that particular SP still no luck

  I then uses DBCC FREEPROCCACHE still no luck

  I then dropped the SP still no luck (still showing the old SP text)

  I then created the SP after a while say 15 minutes i could able to see the new result text for that SP

  Is there any bug in SP_helptext?

  from where the SP_helptext reads the SPs text from?

  How can a dropped SP still be accessible using sp_helptext?

  Cheers

• Adiga

  One Orange Chip

  Points: 27284

  More actions

  December 8, 2010 at 2:57 am

  #1259958

  I followed the same steps on an instance running SQL Server 2005. But I could not reproduce the issue.

  sp_helptext gets the information from syscomments view.

  Pradeep Adiga
  Blog: sqldbadiaries.com
  Twitter: @pradeepadiga

• nadabadan

  SSCommitted

  Points: 1503

  More actions

  December 9, 2010 at 1:28 pm

  #1260771

  I'm betting that you dropped/created the sp in the wrong database, perhaps the master database.