SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored Procedure « Journey to SQL Authority with Pinal Dave

SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored Procedure

December 10, 2006 by pinaldave

Following code will help to find all the Stored Procedures (SP) which are related to one or more specific tables. sp_help and sp_depends does not always return accurate results.

—-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%’