Wednesday, March 21, 2012

How to find the table used in Stored Procedure By Query

Hi,

I need to a find table which is used by list of stored procedures.

Can you please send me the query which is used?

Thanks and Regards

Abdul M.G

There is no built-in function in SQL Server that can do this. You need to search sysobjects table for matching strings.

Here's a piece of code I found that will list all stored procedures that reference a certain table.

SELECT DISTINCT so.name FROMsyscomments scINNERJOINsysobjects soon sc.id=so.idWHERE sc.textLIKE'%tablename%'

[Original source here]

|||

A much simpler way is to get the output of the sp_depends system stored procedure. This will give you any tables, views, stored procedures, user-defined functions or triggers used by the given stored procedure. Usage is sp_depends 'YourSPName'. Required tables will have a value of 'user table' in the type field in the output.

No comments:

Post a Comment