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

Question

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

Answer 1

@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

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

Follow this question

Related Questions