Find specific text in Database

Hi again...!
In this post I'm going to show you how to find a specific text in  stored procedures, views or table definitions. When database tables, SPs or views increase day by day it is impossible to remember everything in our mind. Few days ago I've come across a situation, where a database table in a production environment getting update incorrectly. I have to find the root cause & after few hours of analyzing we got nothing but a column name, which is updating incorrectly, caused the error.

Then I have to find all the places (all the stored procedures) where that column updating. This is the sql query I've used in that case.

USE Your_DB_Name
GO
SELECT o.name, m.definition, o.type_desc
FROM sys.sql_modules m 
INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.definition LIKE '%employee%'; /*put your search text */

Use a SELECT * and check all the data available for you. Also you can modify this query to find specific text in tables, views, table types etc. Just change the FROM clause (change "sql_modules" into tables, table_types or views)

If you want to find something like "[your_text]" (text with square brackets) use escape character as following

LIKE '%[[your_text]]%' /*wrap text with aextra pair of square brackets*/ 

OR

LIKE '%\[your_text\]%' ESCAPE '\'

Apart from these queries you can use redgate's SQL Search plugin which is freely available good tool.