Searching SQL Server Stored Procedure and Trigger Text
While I like to consider myself a web developer, every now and then I have to put on my DBA hat to address some SQL related issue. This little script has saved my butt more than once. It searches the text of triggers, UDFs, stored procedures and views for a particular substring, returning the name and type of those database objects that match.
DECLARE @Search varchar(255)
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
The above script is one of many in my “bag of scripts” I’ve collected over the years. This particular gem was snagged from Stackoverflow: How to find a text inside SQL Server procedures / triggers?