How to identify which all SPs are accessing a given table ?

I don't know if I am in the right section of the forum. Please help me with this :

Is there any system stored procedure or any other method to identify the list of all stored procedures that are using a particular table in my database.

Thanks

Prasad P

Hi,

the only reliable method to get the information is to search the Information Schemas for the information, there is an article about sp_depends which sounds like you would get reliable information from it, but you don′t. I didn′t check the behaviour in SQL2k5, but check this article to get deeper information:

http://b.wunder.home.comcast.net/16509.htm

HTH, Jens Suessmeyer.

|||Check out my blog site:

http://blogs.claritycon.com/blogs/the_englishman/archive/2006/02/09/197.aspx

After having the same problem, I wrote a blog containing a stored procedure which searches stored procedures for a text string using the syscomments system table (which stores the stored proc definitions). If you pass the table name into this procedure, it should get you in the correct direction.

Let me know if that solves your problem, or whether you need more assistance.

HTH|||? No, there's nothing built in. A quick and dirty way of figuring it out is: SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TEXT LIKE '%YourTableName%' This does have some issues due to especially large routines and routines that make use of dynamic SQL and concatenate names, but it may give you some indication. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Prasad Peesapati@.discussions..microsoft.com> wrote in message news:bdc2238e-c590-47ff-927a-5660b2f552db@.discussions.microsoft.com... I don't know if I am in the right section of the forum. Please help me with this : Is there any system stored procedure or any other method to identify the list of all stored procedures that are using a particular table in my database. Thanks Prasad P