Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Search for a String in all Stored Procedures and beyond? Expand / Collapse
Author
Message
Posted Tuesday, June 10, 2008 7:30 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, August 15, 2014 11:42 AM
Points: 646, Visits: 732
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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, June 13, 2014 5:24 AM
Points: 677, Visits: 469
Instead of commenting out that line, @searchString should be replaced with @SString.
Post #544120
Posted Thursday, July 31, 2008 2:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 24, 2014 3:16 AM
Points: 9, Visits: 85
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
Posted Thursday, July 31, 2008 6:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 8, 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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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/
Post #1037618
Posted Wednesday, October 19, 2011 11:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 4:11 PM
Points: 15, Visits: 94
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

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 12»»

Permissions Expand / Collapse