Tuesday, April 17, 2012

Searching SQL Server Stored Procedure,Trigger text and Column Names

Here is a script to search triggers, stored procs, and column names for a text. Sometimes, you may want to lookup some columns or text inside procs or triggers. System views got lots of views to see tables, columns, and procs. You can use those views to search all columns for a field and do more.

DECLARE @LikeSearch varchar(255)
SET @LikeSearch='status'
o.name AS Object_Name,o.type_desc,  m.definition as Context
FROM sys.sql_modules m
JOIN sys.objects  o 
ON m.object_id = o.object_id
WHERE m.definition Like '%'+@LikeSearch+'%'

select 'Table-Column:'+t.name+'-'+ c.name as Object_Name  from sys.columns c join sys.tables t
on c.object_id = t.object_id
where c.name Like '%'+@LikeSearch+'%'

No comments:

Post a Comment

Let me know what you think?