Script: search for text inside a stored procedure, trigger or constraint in SQL Server

Here are some handy scripts in case you need to search for a particular text inside a stored procedure, trigger or constraint on SQL Server. This can come up for example if a new function has changed or your development team is working on some modification that might affect several objects. Just replace the wilcard (or the WHERE clause in general) and run on the database you’re searching on.

Search for text inside a stored procedure

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

 

Search for text inside a trigger

SELECT TR.NAME AS OBJECT_NAME, TR.TYPE_DESC, SM.DEFINITION
   FROM SYS.SQL_MODULES SM
       INNER JOIN SYS.TRIGGERS  TR ON SM.OBJECT_ID=TR.OBJECT_ID
WHERE SM.DEFINITION LIKE '%wildcard%'

 

Search for text inside a constraint

SELECT CONSTRAINT_NAME,CHECK_CLAUSE
FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS
WHERE CHECK_CLAUSE LIKE '%wildcard%'

 

 

Cheers!



Warner Chaves
Author: Warner Chaves
SQL Server Certified Master and MVP. DBA for MS technologies for over 10 years.

2 Comments

  • Avatar

    Nice Article,
    I am also using below script to find text in sql server:
    SELECT *FROM sys.objects
    WHERE OBJECT_DEFINITION (OBJECT_ID) LIKE ‘%Employee%’

    Please find more DBA script at:
    http://www.dbrnd.com/category/script/

  • Avatar
    kiquenet

    Valid for views, stored procedures, user defined tables, triggers, and for functions, Constraints, Rules, Defaults ?

    _Just be aware that the syscomments table stores the values in 8000-character chunks, so if you are unlucky enough to have the text you’re searching for split across one of these boundaries, you won’t find it with this method_ via http://stackoverflow.com/a/4222372/206730

    syscomments problems 8000 characters:

    SELECT o.name, o.type, o.xtype, c.text
    , USER_NAME(o.uid) + ‘.’ + OBJECT_NAME(c.id) AS ‘Object name’
    FROM syscomments c
    JOIN sysobjects o
    ON c.id = o.id
    where c.text LIKE ‘%NumeroTotal%’ or c.text LIKE ‘%NumeroUsadas%’

    sys.sql_modules not valid for constraints and others:

    SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, o.type, o.type_desc, sm.definition
    FROM sys.sql_modules AS sm
    JOIN sys.objects AS o ON sm.object_id = o.object_id
    where sm.definition like ‘%NumeroTotal%’ or sm.definition LIKE ‘%NumeroUsadas%’ collate SQL_Latin1_General_CP1_CI_AS
    ORDER BY o.type;

Leave a Reply