Jan 7, 2015

Find all store procedure related to table in SQL Server

Problem: Sometimes we need to find procedures where a particular table has been used.

Solution: Following query may help you to find all procedure in which particular table exist. It may be also use to find text use in procedures.

SELECT DISTINCT a.name,b.text,a.crdate
FROM sys.syscomments b
INNER JOIN sys.sysobjects a ON b.id=a.id
WHERE b.TEXT LIKE '%tblName%'

Note: sc.text return all the text of store procedure.