Quick tip: Search for a term in a Stored Procedure

Need to find out if a term is referenced in any stored procedures in your database?  Use the following T-SQL statement…

select name, definition
from sys.procedures p
    inner join sys.sql_modules m
        on p.object_id = m.object_id
where
    m.definition like ‘%search term%';

This will work in SQL Server 2005 and 2008.

4 comments

  1. mohammad ghoreishy

    hi alan,i have a problem with management studio2008in “Object Explorer” -> “Connect” there isn’t “Reporting Services”. just there are :”database engine” and “sql server compact…”. i want to work with reporting service from management studio 2008.

  2. Alan

    I’m not totally sure, but I think you may need the full version of SSMS, not Express. You should be able to get this if you have Developer Edition.

Leave a Reply