identify stored procedures and triggers that carries a particular string in sql server

In sql server 2005

I need to identify all the stored procedures and triggers where a particular table name is used

for ex: i wa开发者_开发知识库nt to search for "Table1"

Please advise

In SQL2005:

SELECT Name 
FROM sys.procedures 
WHERE OBJECT_DEFINITION(object_id) LIKE '%Table1%' 

Or in SQL Server Management Studio right-click on the table and choose "View dependencies", but this wont find procs that reference Table1 using dynamic SQL.

More info: http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html

Download SQL Digger - works a treat

http://www.sqldigger.com/

Try one of these:

select sysobjects.name from syscolumns left join sysobjects on sysobjects.id = syscolumns.id where syscolumns.name like '%Table1%' order by 1

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Table1%'
GO