One of my colleagues sent me a very handy bit of code this morning that I thought would be helpful to post.
Below are two options which, when run against the database, should return the names of all stored procedures that reference the named table.
----Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'
----Option 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'
I am told that the functionality of the first option has been verified.
Houston, TX 77002