Friday, June 7

How To Find An Object By Text In SQL Server

How to search for a 'text' in SQL Server. So here is the magic query:

You have to declare a sql variable @Search and set this variable as our text.

This query will get you all the objects under the selected database containing the "text" set in sql variable. And also this will get you the object type. So you are good to know if this is stored procedure or view or anything-else.

DECLARE @Search varchar(255)
SET @Search='[10.10.100.50]'
SELECT DISTINCT
    o.name AS Object_Name,o.type_desc
    FROM sys.sql_modules        m 
        INNER JOIN sys.objects  o ON m.object_id=o.object_id
    WHERE m.definition Like '%'+@Search+'%'
    ORDER BY 2,1

Here is the output of the query looks like:

Text Search in Sql Server
Find By Text in SQL Server