SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Mani Singh / Search for a String in all Stored Procedures and beyond? / Latest Posts

RE: Search for a String in all Stored Procedures and beyond?

dbuendiab — Tue, 31 Jan 2012 03:49:00 GMT

Nobody seems to care about the maybe remote possibility of having the searched string splitted between two rows of syscomments, if the code is big enough to span over 4000 characters. The text column is actually a nvarchar(4000), so it is not uncommon to have a bigger SP and you'll not detect a string splitted in between. I wrote a function to deal with this situation, just joining two syscomments to get in a row the two contiguous text blocks:[code="sql"]CREATE FUNCTION dbo.fn_objects_text_search( @object_name varchar(128), @searched_text varchar(100)) RETURNS intASBEGINRETURN ( SELECT -- Return 1 if any match found, 0 else ---- CASE WHEN EXISTS( -- Mark matches by block ---- SELECT 1 FROM ( SELECT CASE -- Found in the first block ---- WHEN text1 LIKE '%' + @searched_text + '%' THEN 1 -- Found in the second block ---- WHEN text2 LIKE '%' + @searched_text + '%' THEN 1 -- Found in the boundary ---- WHEN RIGHT( text1, LEN( @searched_text ) ) + LEFT( text2, LEN( @searched_text ) ) LIKE '%' + @searched_text + '%' THEN 1 ELSE 0 END AS found FROM ( -- Get first and second 4000 nchar blocks in the same row ---- SELECT c.text as text1, isnull( c2.text, '' ) AS text2 FROM syscomments c INNER JOIN sysobjects o ON c.id = o.id INNER JOIN syscomments c2 ON c.id = c2.id AND c2.colid = c.colid + 1 WHERE name = @object_name ) AS a ) AS b WHERE found <> 0 ) THEN 1 ELSE 0 END AS found)END[/code]

RE: Search for a String in all Stored Procedures and beyond?

richard.jereb — Thu, 27 Oct 2011 05:23:48 GMT

Hi, here a further alternative:[code="sql"]select object_name(object_id) as Namefrom sys.sql_moduleswhere definition like '%your_string_comes_here%'order by Name[/code]In combination with sp_foreachdb this one can also do the job.Cheerio, R.

RE: Search for a String in all Stored Procedures and beyond?

janis.l.murphy — Thu, 20 Oct 2011 09:26:03 GMT

Didn't like the solution. There is an error in it (already noted in a previous post). Added to that, I have a method that also searches views. The extra search of the syscomments.text was applied for very long procedures.[code="sql"]DECLARE @SQL NVARCHAR(MAX) -- Parameter to sp_MSforeachdb-- Parameters into @SQLDECLARE @SearchText varchar(100) = '<Place the String Here>'SET @SearchText = '%' + @SearchText + '%'IF OBJECT_ID('TEMPDB..##Report') IS NOT NULLBEGIN DROP TABLE ##Report;-- Drop the temp table if already existsENDCREATE TABLE ##Report(db VARCHAR(100),[DB Object] VARCHAR(100),[DB Object Type] VARCHAR(100),[Definition] NVARCHAR(MAX),create_date DATETIME,modify_date DATETIME)SET QUOTED_IDENTIFIER OFF; SET @SQL = REPLACE("USE [?]; DECLARE @db VARCHAR(100) = '?';INSERT INTO ##ReportSELECT @db [db],* FROM (SELECT * FROM (SELECT DISTINCT ROUTINE_SCHEMA + '.' + ROUTINE_NAME [DB Object], ROUTINE_TYPE AS [DB Object Type], ROUTINE_DEFINITION, o.create_date, o.modify_dateFROM INFORMATION_SCHEMA.ROUTINES iINNER JOIN (SELECT o.name, c.text [ROUTINE_TEXT], o.create_date, o.modify_date FROM sys.syscomments c INNER JOIN sys.objects o on c.id = o.object_id) O ON i.ROUTINE_NAME = O.nameWHERE PatIndex('@SearchText',ROUTINE_DEFINITION) + PatIndex('@SearchText',ROUTINE_TEXT) > 0UNION ALLSELECT TABLE_SCHEMA + '.' + TABLE_NAME [DB Object], 'VIEW' AS [DB Object Type], VIEW_DEFINITION, o.create_date, o.modify_dateFROM INFORMATION_SCHEMA.VIEWS iINNER JOIN (SELECT o.name, c.text [ROUTINE_TEXT], o.create_date, o.modify_date FROM sys.syscomments c INNER JOIN sys.objects o on c.id = o.object_id) O ON i.TABLE_NAME = O.nameWHERE PatIndex('@SearchText',VIEW_DEFINITION) > 0) A) BORDER BY 1,3,2","@SearchText",@SearchText)SET QUOTED_IDENTIFIER ON;BEGIN TRYEXEC sp_MSforeachdb @SQL;END TRYBEGIN CATCH PRINT ERROR_MESSAGE() PRINT @SQLEND CATCHSELECT * FROM ##Report ORDER BY db,modify_date desc, [DB Object Type],[DB Object];DROP TABLE ##Report;[/code]

RE: Search for a String in all Stored Procedures and beyond?

marcin.motyl — Thu, 20 Oct 2011 06:13:48 GMT

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 ) > 0However 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.

RE: Search for a String in all Stored Procedures and beyond?

Aditya-313343 — Wed, 19 Oct 2011 23:42:38 GMT

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.

RE: Search for a String in all Stored Procedures and beyond?

Mezga Geza — Tue, 21 Dec 2010 04:16:06 GMT

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

RE: Search for a String in all Stored Procedures and beyond?

Craig Doriot — Thu, 26 Feb 2009 20:15:53 GMT

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

RE: Search for a String in all Stored Procedures and beyond?

larry.eisenstein-820674 — Thu, 31 Jul 2008 06:13:24 GMT

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.[code]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 IfElse ' 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 IfEnd If[/code]

RE: Search for a String in all Stored Procedures and beyond?

winash-561361 — Thu, 31 Jul 2008 02:00:27 GMT

The script searches for patterns in INFORMATION_SCHEMA.ROUTINESSET @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.definitionFrom '+ @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'

RE: Search for a String in all Stored Procedures and beyond?

wytze.sijtsma — Thu, 31 Jul 2008 01:35:58 GMT

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

RE: Search for a String in all Stored Procedures and beyond?

timgapinski — Mon, 30 Jun 2008 15:40:14 GMT

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?

RE: Search for a String in all Stored Procedures and beyond?

timgapinski — Mon, 30 Jun 2008 15:11:49 GMT

this script will really help me out, but I am getting an error. Msg 137, Level 15, State 2, Line 21Must declare the scalar variable "@searchString".

Search for a String in all Stored Procedures and beyond?

MannySingh — Tue, 10 Jun 2008 07:30:01 GMT

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