June 10, 2008 at 7:30 am
Comments posted to this topic are about the item Search for a String in all Stored Procedures and beyond?
Maninder
www.dbanation.com
June 30, 2008 at 3:11 pm
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".
June 30, 2008 at 3:40 pm
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?
July 31, 2008 at 1:35 am
Instead of commenting out that line, @searchString should be replaced with @SString.
July 31, 2008 at 2:00 am
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'
July 31, 2008 at 6:13 am
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
February 26, 2009 at 8:15 pm
good stuff.. need to make it work for triggers & functions...
December 21, 2010 at 4:16 am
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:
October 19, 2011 at 11:42 pm
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.
October 20, 2011 at 6:13 am
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.
October 20, 2011 at 9:26 am
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.
DECLARE @sql NVARCHAR(MAX) -- Parameter to sp_MSforeachdb
-- Parameters into @sql
DECLARE @SearchText varchar(100) = '<Place the String Here>'
SET @SearchText = '%' + @SearchText + '%'
IF OBJECT_ID('TEMPDB..##Report') IS NOT NULL
BEGIN
DROP TABLE ##Report;-- Drop the temp table if already exists
END
CREATE 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 ##Report
SELECT @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_date
FROM INFORMATION_SCHEMA.ROUTINES i
INNER 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.name
WHERE PatIndex('@SearchText',ROUTINE_DEFINITION) + PatIndex('@SearchText',ROUTINE_TEXT) > 0
UNION ALL
SELECT TABLE_SCHEMA + '.' + TABLE_NAME [DB Object], 'VIEW' AS [DB Object Type], VIEW_DEFINITION, o.create_date, o.modify_date
FROM INFORMATION_SCHEMA.VIEWS i
INNER 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.name
WHERE PatIndex('@SearchText',VIEW_DEFINITION) > 0
) A
) B
ORDER BY 1,3,2
","@SearchText",@SearchText)
SET QUOTED_IDENTIFIER ON;
BEGIN TRY
EXEC sp_MSforeachdb @sql;
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
PRINT @sql
END CATCH
SELECT * FROM ##Report ORDER BY db,modify_date desc, [DB Object Type],[DB Object];
DROP TABLE ##Report;
October 27, 2011 at 5:23 am
Hi, here a further alternative:
select
object_name(object_id) as Name
from
sys.sql_modules
where
definition like '%your_string_comes_here%'
order by
Name
In combination with sp_foreachdb this one can also do the job.
Cheerio, R.
January 31, 2012 at 3:49 am
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:
CREATE FUNCTION dbo.fn_objects_text_search(
@object_name varchar(128),
@searched_text varchar(100)
) RETURNS int
AS
BEGIN
RETURN (
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
May 12, 2016 at 6:57 am
Thanks for the script.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply