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