x

FInd the underlying tables assocaited with a view or a stored procedure in sql server.

I want to get the name of all tables associated with a view or a procedure.

I have a query:

                     select distinct
         v_obj.name as ViewName,
         t_obj.name as SourceTable,
         v_obj.xtype
         from
         sysobjects t_obj,
         sysobjects v_obj,
         sysdepends dep
         where
         dep.id = v_obj.id
         and dep.depid = t_obj.id
         order by
         v_obj.name,
         t_obj.name

This will return all the tables or views that are associated with a particular object. But the problem is that suppose we have a procedure that is associated with a view.

In this case it will return the name of the view. I want is something that it will return the name of the tables associated with a procedure through a view.

Example:

If a procedure p is dependent upon a View v and table t1. and further v1 is dependent upon table t2 and t3

Then i want is t1, t2 and t3.

Any help is appreciated.

Thanks

more ▼

asked Nov 14, 2011 at 06:37 AM in Default

Kamal Deep Singh Saini gravatar image

Kamal Deep Singh Saini
25 3 3 4

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

@Kamal First of All, you have tagged it in SQL 2005/2008, but your query seems to be for SQL 2000? Do you need solution against SQL 2000?

I can understand what you are trying to achieve but you must keep in mind that till SQL 2005 Dependencies are established during CREATE only if the referenced (independent) entity exists at the time that the referencing (dependent) object is created. Due to deferred name resolution, the referenced entity need not exist at the time of creation. In this case, a dependency row is not created. Moreover, entities referenced through dynamic SQL do not establish dependencies. So you could not be sure that all your dependent objects would be in the list. For SQL 2005 you can run this kind of query

;WITH CTE AS (SELECT   o.name
,       o.type_desc
,       p.name
,       p.type_desc
,     p.object_id
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
UNION ALL
SELECT  o.name
,       o.type_desc
,       p.name
,       p.type_desc
,     p.[object_id]
FROM    sys.sql_dependencies d
        INNER JOIN CTE o
        ON d.object_id = o.object_id
        INNER JOIN sys.objects p
        ON d.referenced_major_id = p.object_id

       ) 
SELECT DISTINCT * FROM [CTE]

But the game changes with SQL Server 2008. You can track the dependency no matter whether the referenced object exists or not. Below are some good hyperlinks to start with and you should be able to sort out your problem as it is much easier in SQL 2008

http://msdn.microsoft.com/en-us/library/ms345449(v=SQL.100).aspx

[http://msdn.microsoft.com/en-us/library/bb677168(v=SQL.100).aspx][2]

[2]: http://msdn.microsoft.com/en-us/library/bb677168(v=SQL.100).aspx
more ▼

answered Nov 15, 2011 at 11:40 PM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1951
x1850
x292

asked: Nov 14, 2011 at 06:37 AM

Seen: 4735 times

Last Updated: Nov 14, 2011 at 06:45 AM