Script Repo SQL_Server_Logo_svg

Published on January 18th, 2015 | by Warner Chaves

2

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!

Tags: , , ,


About the Author

SQL Server Certified Master and MVP. DBA for MS technologies for over 10 years.



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

  1. anveshpatel88@gmail.com' Anvesh Patel says:

    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/

  2. kiquenet+dev+sqlturbo@gmail.com' kiquenet says:

    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 to Anvesh Patel Cancel reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Back to Top ↑
  • Sign Up For Our Newsletter

    Stay up to date on the latest from SQL Server products and Features.

    Sign Up Now

    Sign up for mailing list and receive new posts directly to your inbox.

    We promise never to share or sell any of your personal information.

  • Recent Posts

  • Categories

  • Archives

  • Tags

  • Topics

  • Subscribe to our RSS Feed


    sqlturbo.com A SQL Server Web Blog
    Canada, Ontario, Ottawa.
    http://sqlturbo.com

    The Authors