SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Search for a String in all Stored Procedures and beyond?


Search for a String in all Stored Procedures and beyond?

Author
Message
MannySingh
MannySingh
SSCertifiable
SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)SSCertifiable (5K reputation)

Group: General Forum Members
Points: 5023 Visits: 787
Comments posted to this topic are about the item Search for a String in all Stored Procedures and beyond?

Maninder
www.dbanation.com
timgapinski
timgapinski
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 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".
timgapinski
timgapinski
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 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?
wytze.sijtsma
wytze.sijtsma
Right there with Babe
Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)Right there with Babe (725 reputation)

Group: General Forum Members
Points: 725 Visits: 470
Instead of commenting out that line, @searchString should be replaced with @SString.
winash-561361
winash-561361
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 103
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'
larry.eisenstein-820674
larry.eisenstein-820674
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
Points: 78 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


Craig Doriot
Craig Doriot
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 18
good stuff.. need to make it work for triggers & functions...
Mezga Geza
Mezga Geza
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 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
Aditya-313343
Aditya-313343
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 134
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.
marcin.motyl
marcin.motyl
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search