Jul 22, 2016

Find all stored procedures related to table in database in SQL Server

There are many different ways to find all stored procedures related to specific table, some are described below.

Using sp_depends: This is the system stored procedure which can use to find all users defined stored procedures related to specific table.


USE[DB_NAME]
GO

EXEC sp_depends @objname = N'TBL_NAME' ;
GO


Note: sp_depends does not always return accurate results.

See following script this will help you to find stored procedures related to specific table.


----Script 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'

----Script 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%' 



Referral sites: http://blog.sqlauthority.com/2006/12/10/sql-server-find-stored-procedure-related-to-table-in-database-search-in-all-stored-procedure/