Get the Names of Stored Procedures that Reference a Given Table in SQL Server

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

Leave a Reply