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 Thursday, October 20, 2011 9:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 8:52 AM
Points: 37, Visits: 115
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;

Post #1193841
Posted Thursday, October 27, 2011 5:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 22, 2014 2:35 AM
Points: 28, Visits: 60
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.
Post #1196829
Posted Tuesday, January 31, 2012 3:49 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 11:41 AM
Points: 77, Visits: 218
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

Post #1244198
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse