How to find the tables being affected, the actually relationship, and type of query used in the stored procedure?

hi Darnell,

use following query that help you to find all table that are used in SP

SELECT o.name AS procname, oo.name AS tablename
FROM sysdepends d
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
WHERE o.xtype = 'P'

hope this help.

I having trouble trying to pull the tables the stored procedures are using and also the relationships the stored procedures are using, can someone assist me

hi,

In SQL server there is one table that store relation ship "sysdepends".

Try....

SELECT o.name AS procname, oo.name AS tablename,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sysdepends d
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
WHERE o.xtype = 'P'
And o.name = 'Procedure Name'

hope this help.

Hi,
you can use SQL Server Profiler to trace your sql queries and stored procedures on the database.



pls refer this url to connet sql profiler

http://www.quackit.com/sql_server/tutorial/sql_profiler.cfm
http://msdn.microsoft.com/en-us/library/ms187929%28SQL.90%29.aspx




thanks


If this post is useful then mark it as "Accepted Answer"

Hi Darnell,

There are ways to identify these things.

Relationship:

You can use the system stored procedure to identify the relationship for the particular table.

sp_depends Table_Name

Type of query used:
You need to search the particular text in the stored procedure.