Ask - Tables , Views, Functions used by Stored Procs in Database

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Ask

Ask questions, discuss or help others by answering


	Ask a question


	Subscribe..

Tables , Views, Functions used by Stored Procs in Database

May 3 2012 12:00AM by vishalchitrala@gmail.com

Hi ,

Is there any way to know Tables, Views and Functions used by a stored procedure in Database... I tried using the below Query but It does not show all the tables. any help is appreciated.

SELECT 
	Table_Name,
	proc_name  AS [Object_Name], 
	'P'  As [Type] 
FROM ( 
	SELECT 
		oo.name AS table_name,
		o.name AS proc_name,
		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'
) DP 
WHERE row = 1

Submitted under: Microsoft SQL Server · TSQL · · ·

vishalchitrala@gmail.com

286 · 0% · 148

1 Replies

try this query

SELECT DISTINCT  o.name
,       o.type_desc
,       p.name
,       p.type_desc
FROM    sys.sql_dependencies d
        INNER JOIN sys.objects o
        ON d.object_id = o.object_id
        INNER JOIN sys.objects p
        ON d.referenced_major_id = p.object_id
WHERE o.name='storedprocedurename' AND o.type_desc='SQL_STORED_PROCEDURE'

commented on May 4 2012 7:30AM

Mitesh Modi

18 · 10% · 3078

Your Reply

Sign Up or Login to post a comment.