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


There are many different ways to find all stored procedures related to the 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 the stored procedures related to the 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/ 

Related Posts

Fatal Error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails.

Problem: Sometimes, we get the following error when trying to make a connection in Rails.  FATAL ERROR: Peer authentication failed for us...