x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

Interogate Sprocs

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

Print | posted on Thursday, February 05, 2004 2:12 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# 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.
2/5/2004 2:56 PM | Tara
Gravatar

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

2/5/2004 3:19 PM | Brett
Gravatar

# re: Interogate Sprocs

Dude! The SQL Warrior princess was here.

Nice proc. I'll try it out.
2/5/2004 3:20 PM | SamC
Gravatar

# re: Interogate Sprocs

Nice Proc Brett ... but you can simply query from syscomments .... why go all the way
2/5/2004 4:02 PM | Enigma's Weblog
Gravatar

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

2/5/2004 4:26 PM | Brett
Gravatar

# re: Interogate Sprocs

Vyas' script goes against syscomments anyway. And it supports UDFs and triggers as well. It does not give line numbers.
2/5/2004 4:43 PM | Tara
Gravatar

# 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?

2/5/2004 4:53 PM | Brett
Gravatar

# 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.
2/5/2004 5:28 PM | Tara
Gravatar

# re: Interogate Sprocs

Just for anyone reading....Rob's thread has a VERY elegant solution for searching...

2/6/2004 11:02 AM | Brett
Gravatar

# re: Interogate Sprocs

Brett ... Thanks for the help ... See this ...
http://sqljunkies.com/WebLog/Enigma/archive/2004/02/07/995.aspx
2/7/2004 3:30 PM | Enigma
Gravatar

# 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
6/21/2004 2:29 PM | Kristen
Gravatar

# 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.
9/11/2005 10:39 AM | sqldevl
Gravatar

# 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.
9/12/2005 8:58 AM | Brett
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET