Interogate Sprocs

Thu Feb 5, 2004 by Brett Kaiser (x002548) in sql-server

Seen a bunch of people interested in Searching through Sprocs lately. Thought it would be handy (especially for some of these god awful legacy database we're so fortunate to inherit.

Just script the sprocs in to individual files and copy to the server (if they're not there already)

And execute the code below.

USE Northwind
GO

CREATE TABLE Folder (dir_output varchar(8000))
CREATE TABLE Folder_Parsed (Create_Time datetime, File_Size int, [File_Name] sysname)
CREATE TABLE MyWork99 (myText99 varchar(7000))
CREATE TABLE MySprocs99 (myText99 varchar(7000), SprocName sysname NULL, LineNum int IDENTITY(1,1))
GO

SET NOCOUNT ON

INSERT INTO Folder EXEC master..xp_cmdshell 'Dir d:\Sprocs\*.*'

INSERT INTO Folder_Parsed (Create_Time, File_Size, [File_Name] )
SELECT   Convert(datetime,Substring(dir_output,1,8)
+ ' '
+ (Substring(dir_output,11,5)
+ Case When Substring(dir_output,16,1) = 'a' Then ' AM' Else ' PM' End)) As Create_Time
, Convert(Int,LTrim(RTrim(Replace(Substring(dir_output,17,22),',','')))) As File_Size
, Substring(dir_output,40,(Len(dir_output)-39)) As File_Name
From    Folder
Where Substring(dir_output,1,1) <> ' '
   And (Substring(dir_output,1,1) <> ' '
   And Substring(dir_output,25,5) <> '

DECLARE @File_Name sysname, @cmd varchar(8000)

DECLARE myCursor99 CURSOR
FOR
SELECT [File_Name] FROM Folder_Parsed WHERE [File_Name] LIKE '%.PRC'

OPEN myCursor99

FETCH NEXT FROM myCursor99 INTO @File_Name

WHILE @@FETCH_STATUS = 0
BEGIN

TRUNCATE TABLE myWork99

SELECT @cmd = 'BULK INSERT myWork99 FROM '
   + ''''
   + 'D:\Sprocs\'+@File_Name
   + ''''
   + ' WITH (DATAFILETYPE = '
   + ''''
   + 'widechar'
   + ''''
   + ')'

EXEC(@cmd)

INSERT INTO mySprocs99(myText99) SELECT myText99 FROM myWork99

UPDATE mySprocs99 SET SprocName = @File_Name WHERE SprocName IS NULL

FETCH NEXT FROM myCursor99 INTO @File_Name
END

CLOSE myCursor99
DEALLOCATE myCursor99
GO

SET NOCOUNT OFF

-- Do Analysis

   SELECT (LineNum-MIN_LineNum)+1 AS Line_Number, myText99
     FROM mySprocs99 l
LEFT JOIN (
  SELECT   SprocName
   , MAX(LineNum) AS MAX_LineNum
   , MIN(LineNum) AS MIN_LineNum
      FROM mySprocs99 GROUP BY SprocName
   ) AS r
      ON l.SprocName = r.SprocName
   WHERE l.SprocName = 'dbo.GetUserAuth_sp.PRC'
GO

-- Clean up When Done

DROP TABLE Folder
DROP TABLE Folder_Parsed
DROP TABLE myWork99
DROP TABLE mySprocs99
GO

Legacy Comments


Tara 2004-02-05	re: Interogate Sprocs You don't need to script out the stored procs to files. Just use Vyas' stored proc to search for you: http://vyaskn.tripod.com/code/search_stored_procedure_code.txt He provides an example of how to run it in the comment header block.

Brett 2004-02-05	re: Interogate Sprocs Yes and then it spits out a nice sp_helptext command for you then to search through.... I guess scripting out all the sprocs is a bit of a pain, but if you alread have a library of current scripts it shouldn't be to hard... Vyas' method is definetily more current than mine though. My is only as accurate as to the last time of the scripting...any changes after that won't be reflected, unless it's rescripted. But that's what change control is for.

SamC 2004-02-05	re: Interogate Sprocs Dude! The SQL Warrior princess was here. Nice proc. I'll try it out.

Enigma's Weblog 2004-02-05	re: Interogate Sprocs Nice Proc Brett ... but you can simply query from syscomments .... why go all the way

Brett 2004-02-05	re: Interogate Sprocs syscomments is kinda messy don't you think? The sproc also gives you line numbers AND isloates the catalalog...no? Always more than 1 way to skin a cat...

Tara 2004-02-05	re: Interogate Sprocs Vyas' script goes against syscomments anyway. And it supports UDFs and triggers as well. It does not give line numbers.

Brett 2004-02-05	re: Interogate Sprocs Yeah...I should expand it to all objects... Just curious as to the impact to the catalog/database if you built a process that kept banging away at the system tables?

Tara 2004-02-05	re: Interogate Sprocs Why would you build a process around it though? Getting information like this would be an occasional thing, not something that you would schedule or put inside your application. IMO, at least.

Brett 2004-02-06	re: Interogate Sprocs Just for anyone reading....Rob's thread has a VERY elegant solution for searching...

Enigma 2004-02-07	re: Interogate Sprocs Brett ... Thanks for the help ... See this ... http://sqljunkies.com/WebLog/Enigma/archive/2004/02/07/995.aspx

Kristen 2004-06-21	re: Interogate Sprocs Brett, we Encrypt ALL SProcs on production databases (just to make life harder for anyone who does manage to hack in there), so grabbing them from files is just fine for me. I've been meaning to get them copied from the O/S to the DB for ages anyway, now I've got a leg-up to help, thanks. Kristen

sqldevl 2005-09-11	fantastic one WOW thanks a lot man. i had a requirement like getting all the comments from the sql file. i just modified this sp and used the css file wow exactly what i was looking for thank you by the way who wrote this script thanks a lot man.

Brett 2005-09-12	Your welcome Well, since it's my blog, I did. Any other code that is not mine will always have the author and source credited.