How do I find a stored procedure containing <text>?

Question

I need to search a SQL server 2008 for stored procedures containing where maybe the name of a database field or variable name.

Solution 1

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%Foo%' 
    AND ROUTINE_TYPE='PROCEDURE'

---

SELECT OBJECT_NAME(id) 
    FROM SYSCOMMENTS 
    WHERE [text] LIKE '%Foo%' 
    AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
    GROUP BY OBJECT_NAME(id)

---

SELECT OBJECT_NAME(object_id)
    FROM sys.sql_modules
    WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
    AND definition LIKE '%Foo%'

Solution 2

Grab yourself a copy of the free Red-Gate SQL Search tool and start enjoying searching in SQL Server! :-)

It's a great and very useful tool, and YES! it's totally, absolutely FREE for any kind of use.

Solution 3

I took Kashif's answer and union'd all of them together. Strangely, sometimes, I found results in one of the selects but not the other. So to be safe, I run all 3 when I'm looking for something. Hope this helps:

DECLARE @SearchText varchar(1000) = 'mytext';

SELECT DISTINCT SPName 
FROM (
    (SELECT ROUTINE_NAME SPName
        FROM INFORMATION_SCHEMA.ROUTINES 
        WHERE ROUTINE_DEFINITION LIKE '%' + @SearchText + '%' 
        AND ROUTINE_TYPE='PROCEDURE')
    UNION ALL
    (SELECT OBJECT_NAME(id) SPName
        FROM SYSCOMMENTS 
        WHERE [text] LIKE '%' + @SearchText + '%' 
        AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
        GROUP BY OBJECT_NAME(id))
    UNION ALL
    (SELECT OBJECT_NAME(object_id) SPName
        FROM sys.sql_modules
        WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
        AND definition LIKE '%' + @SearchText + '%')
) AS T
ORDER BY T.SPName

Solution 4

You can also try ApexSQL Search - trial version SSMS plug-in from ApexSQL.

Solution 5

In case you needed schema as well:

SELECT   DISTINCT SCHEMA_NAME(o.schema_id),o.name,[text]
FROM     syscomments AS c
         INNER JOIN sys.objects AS o ON c.id = o.[object_id]
         INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id
WHERE    text LIKE '%foo%'
ORDER BY  SCHEMA_NAME(o.schema_id),o.name 

View more solutions

Comments

[user]

Be careful with this, though, as it is only a substring match. "FieldName" might occur in a comment, or a string constant, or anything like that. Also, I doubt looking for [whatever] will catch whatever (becomes more important when you are looking for schema-qualified identifiers).

[PeteT]

Be aware routine_definition is cropped at 4000 chars just in case you have a long procedure. Use OBJECTPROPERTY method instead.

[Eric Burdo]

I used a variation of this (with a variable and looking for a specific proc) to get the Proc Body contents (I had to dump the [text] into a variable, then print it to see the full proc).

[TMcManemy]

Per the execution plan, the third variation appears to be the most performant way of getting at this data.

[TMcManemy]

See @PeteT's comment in the accepted answer, above. INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION is cropped at 4000 characters. As such, this method will not work if you have stored procedures longer than 4000 characters.

[Scott]

This is an excellent tool, which I had already been using before I came upon this question. It is worth noting that it limits search results to 150 by default. Check out this forum post for info on how to increase that limit.

[Vivian River]

A million times what @PeteT said. This 4000 character limit really bit me a few years ago.

[Goldfish]

to include the schema name: OBJECT_SCHEMA_NAME(OBJECT_ID) AS [Schema]

[Tot Zam]

Can you add some explanation of what each of the 3 queries do, and what the difference between them are?

[DavidScherer]

Would be nice to have some details on what each query is doing and how they're different or why one is better than the other. These kind of answers encourage blind/copy paste by less experienced engineers, and frustration for more senior engineers working with a new RDBMS such as SQL Server.

[Przemyslaw Remin]

Does it also crop stored proc to 4000 characters?

[Zeek2]

First example works well but for databases using multiple schemas I recommend adding ROUTINE_SCHEMA to the list of fields displayed ;) e.g. SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION ROUTINE_SCHEMA FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%DataConsumerCountry%' AND ROUTINE_TYPE='PROCEDURE'

[HardCode]

This returns ALL stored procedures, ignoring the search text.