Search for a String in all Stored Procedures and beyond?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Mani Singh » Search for a String in all Stored Procedures...

Add to briefcase

13 posts, Page 1 of 2

1 2 »»

Search for a String in all Stored Procedures and beyond?

Expand / Collapse

Author

Message

Posted Tuesday, June 10, 2008 7:30 AM

SSChasing Mays

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 1:10 PM
Points: 646, Visits: 729

Comments posted to this topic are about the item Search for a String in all Stored Procedures and beyond?

Maninder
www.dbanation.com

Post #514404

Posted Monday, June 30, 2008 3:11 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 30, 2008 3:35 PM
Points: 2, Visits: 1

this script will really help me out, but I am getting an error.

Msg 137, Level 15, State 2, Line 21
Must declare the scalar variable "@searchString".

Post #526251

Posted Monday, June 30, 2008 3:40 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, June 30, 2008 3:35 PM
Points: 2, Visits: 1

well I can fix declare issue by just commenting out the extra string option. I also only have one database i need to search. when I search all the db's I get errors because of a corrupted db. Which I will delete.

but when I run this on just the database i need it to run on, I get records with empty results . Using sql express I see Database_Name as a column header stored procedure Name as a column header and Routine Definition as a Column header, but no data in the records?

Post #526264

Posted Thursday, July 31, 2008 1:35 AM

Say Hey Kid

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 4:14 AM
Points: 676, Visits: 466

Instead of commenting out that line, @searchString should be replaced with @SString.

Post #544120

Posted Thursday, July 31, 2008 2:00 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 09, 2013 12:26 PM
Points: 9, Visits: 54

The script searches for patterns in INFORMATION_SCHEMA.ROUTINES

SET @sqlstm = 'Select Specific_Catalog as Database_Name, Routine_Name as ''Stored Procedure Name'',Routine_Definition
From '+ @getdbname+'.Information_Schema.Routines
Where PatIndex('+''''+@SString+''''+', Routine_Definition) > 0'

The ROUTINE_DEFINITION column here is nvarchar(4000) and only one row is stored per stored procedure/function. Any stored procedure/function that are bigger than nvarchar(4000) will not have the extra contents searched for the pattern.

It would be better to use sys.sql_modules (checking on the definition column there which is nvarchar(max) and hence will store all the stored procedure/function content)

SET @sqlstm = '
Select T1.Specific_Catalog as Database_Name, T1.Routine_Name as ''Stored Procedure Name'',T2.definition
From '+ @getdbname+'.Information_Schema.Routines T1 INNER JOIN '+ @getdbname+'.sys.sql_modules T2
ON T1.ROUTINE_NAME = OBJECT_NAME(T2.object_id)
Where PatIndex('+''''+@SString+''''+', T2.definition) > 0'

Post #544140

larry.eisenstein-820674

Posted Thursday, July 31, 2008 6:13 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, March 29, 2010 3:06 PM
Points: 6, Visits: 16

This is pretty standard stuff. It's nice, but needs more options.
It does not work on SQL Server 7 (I know, but our company is cheap and doesn't want to upgrade some of our applications).

I wrote an ASP page that does the same, but it also determines if it is SQL 7 or higher, and there is an option to let you choose a DB or search all DB's on the server.
Yes, it's ugly, but it works. I might add more to it later. The lines that start with sqlCmd.CommandText are the ones containin the sql code.

You'll have to play with this a little to get it working in SQL server, b/c I've had to quote a lot of things to format it for use in an ASP(vb.net) page.

If (Database.Equals("")) Then   'Search All Databases
   If (ver < 8) Then
   sqlCmd.CommandText = "exec sp_MSForEachDB @command1='use [?];SELECT OBJECT_NAME(id) as ROUTINE_CATALOG, ''[?]'' as ROUTINE_NAME, '' '' as ROUTINE_TYPE, '' '' as CREATED FROM syscomments  WHERE [text] LIKE ''%" + SearchString + "%'' AND OBJECTPROPERTY(id, ''IsProcedure'') = 1 GROUP BY OBJECT_NAME(id); '"
   Else  
    sqlCmd.CommandText = "exec sp_MSForEachDB @command1='use [?];SELECT ROUTINE_CATALOG, ROUTINE_NAME, ROUTINE_TYPE, CREATED FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE ''%" + SearchString + "%'' AND (ROUTINE_TYPE=''PROCEDURE'' or ROUTINE_TYPE=''FUNCTION''); '"
    End If

Else   ' Search only the specified Database
     If (ver < 8) Then
       sqlCmd.CommandText = "use " + Database + ";SELECT OBJECT_NAME(id) as ROUTINE_CATALOG, '" + Database + "' as ROUTINE_NAME, ' ' as ROUTINE_TYPE, ' ' as CREATED FROM syscomments  WHERE [text] LIKE '%" + SearchString + "%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1 GROUP BY OBJECT_NAME(id);"
     Else
       sqlCmd.CommandText = "use " + Database + ";SELECT ROUTINE_CATALOG, ROUTINE_NAME, ROUTINE_TYPE, CREATED FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%" + SearchString + "%' AND (ROUTINE_TYPE='PROCEDURE' or ROUTINE_TYPE='FUNCTION');"
     End If


End If

Post #544310

Posted Thursday, February 26, 2009 8:15 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, October 08, 2009 1:35 PM
Points: 8, Visits: 18

good stuff.. need to make it work for triggers & functions...

Post #665463

Posted Tuesday, December 21, 2010 4:16 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, December 21, 2010 4:12 AM
Points: 2, Visits: 9

here is another method which searches in stored procedured and also in triggers and functions:
http://it.expertmonster.com/question/How-to-search-in-stored-procedures-108.html

http://it.expertmonster.com/ Discuss

Discuss

Post #1037618

Posted Wednesday, October 19, 2011 11:42 PM

Grasshopper

Group: General Forum Members
Last Login: Sunday, May 19, 2013 10:35 PM
Points: 11, Visits: 53

Try this.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%foobar%'
AND ROUTINE_TYPE='PROCEDURE'

Replace foobar with the string you are searching inside a stored procedure.

This query lists out all the stored procedures in the databse which are using that particulat string.

Hope this helps.

Post #1193336

Posted Thursday, October 20, 2011 6:13 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, October 27, 2011 7:22 AM
Points: 17, Visits: 13

I've tested this query against sample DB and against my current search query:

select distinct object_name(sc.id) [name]
from syscomments sc
Where PatIndex('%<text, varchar(200),>%' , text ) > 0

However posted query is a little bit faster, it does not work correctly. In my test, my procedure returned 2 more SP, and thay had a string I looked for.

Post #1193592

« Prev Topic | Next Topic »

Add to briefcase

13 posts, Page 1 of 2

1 2 »»

Permissions

Expand / Collapse