Microsoft SQL Server: Search for text inside Database Objects

One handy feature missing from SQL Server Management Studio is the ability to search for a piece of text inside stored procedures or other database objects.

SQL query below will do this for you – it will search for a piece of given text inside stored procedures, triggers, views and scalar functions. This works on SQL Server 2005 and 2008.

SELECT o.type_desc AS ROUTINE_TYPE
        ,o.[name] AS ROUTINE_NAME
        ,m.definition AS ROUTINE_DEFINITION
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
    ON m.object_id = o.object_id
WHERE m.definition LIKE '%search text goes here%'
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s