Text Search In SQL Server

How good is that if we can search for a text or string in our whole database?, Whether its a procedure, table or view the query should return the objects containing the string or char passed.

I am thankfull of Nandan Kumarawat who gave me this one. After asking him same thing time to time I blogged it here to make his life easy 😛

So let’s see some code now.

Query get you all the objects under the selected database containing the “text” set in sql variable. And also 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

And here the output looks like:

Cheers!!

Advertisements

Say something : I accept all the "Humer&Critic"

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