Search for a String in all Stored Procedures and beyond?

  • MannySingh

    SSChampion

    Points: 12465

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

  • timgapinski

    SSC Enthusiast

    Points: 176

    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

    SSC Enthusiast

    Points: 176

    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

    Right there with Babe

    Points: 797

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

  • winash-561361

    SSC Enthusiast

    Points: 119

    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

    SSC Enthusiast

    Points: 168

    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

    SSC Journeyman

    Points: 80

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

  • Mezga Geza

    SSC Journeyman

    Points: 98

    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

    Right there with Babe

    Points: 765

    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

    SSC Enthusiast

    Points: 119

    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.

  • janis.l.murphy

    SSC Veteran

    Points: 239

    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;

  • richard.jereb

    SSC Veteran

    Points: 208

    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.

  • dbuendiab

    SSC Eights!

    Points: 993

    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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply