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.

Jignesh Trivedi · Answer

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.

David Smith · Answer

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

Jignesh Trivedi · Answer

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.

MuthuMari M · Answer

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 "

Senthilkumar · 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. 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

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

Answers (5)