Script for sp text

• sbendayan

  SSCommitted

  Points: 1791

  More actions

  May 23, 2002 at 9:42 am

  #78684

  Does anyone have a SQL script that will return ALL the SQL text for all the stored procedures that reference a certain table?

• Antares686

  SSC Guru

  Points: 125444

  More actions

  May 23, 2002 at 10:43 am

  #429672

  First go here and pick up the script to find a string in a database object. http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=184 you will need to install to master db.

  Then try this (note: wrote to get only SPs does not account for reference thru views that would be possible, a ittle work should fix.)

  SET NOCOUNT ON

  CREATE TABLE #SPNames (

  [idx] [int] IDENTITY(1,1) NOT NULL,

  [spname] [varchar](255) NOT NULL

  )

  INSERT INTO #SPNames (spname) EXEC sp_FindStringInCode 'tablenamehere', 'P'

  DECLARE @loop INT

  DECLARE @max-2 INT

  DECLARE @gettext VARCHAR(255)

  SET @loop = 0

  SET @max-2 = (SELECT MAX(idx) AS midx FROM #SPNames)

  WHILE @loop < @max-2

  BEGIN

  SET @loop = @loop + 1

  SELECT @gettext = spname FROM #SPNames WHERE idx = @loop

  EXEC sp_helptext @gettext

  END

  DROP TABLE #SPNames

  Hope this helps.

  "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

• sbendayan

  SSCommitted

  Points: 1791

  More actions

  May 23, 2002 at 11:03 am

  #429677

  you are 'the man'.....

• sbendayan

  SSCommitted

  Points: 1791

  More actions

  May 23, 2002 at 11:56 am

  #429682

  what about if I just wanted to return the joined table columns instead of all the text of the stored proc?