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

Question

How to find the tables being affected, the actually relationship, and type of query used in the or inside the stored procedure? I am making a drill down basically.

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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"

Answer 5

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.

SELECT * FROM syscomments WHERE TEXT LIKE '%select%' AND TEXT LIKE '%procedure%'

SELECT * FROM syscomments WHERE TEXT LIKE '%insert%' AND TEXT LIKE '%procedure%'

SELECT * FROM syscomments WHERE TEXT LIKE '%update%' AND TEXT LIKE '%procedure%'

SELECT * FROM syscomments WHERE TEXT LIKE '%delete%' AND TEXT LIKE '%procedure%'

For identify being tables affected, you can use the triggers on database.

http://www.mindstick.com/Articles/f7074849-0e9e-463b-a12d-f3d7a35b2785/?Trigger%20in%20SQL%20Server