How can i check sql server 'views' dependencies

Question

Is there a way that i can find out what base tables are being used by views using a custom query or stored procedure?

Answer 1

Here's a good explanation:
http://blogs.techrepublic.com.com/datacenter/?p=277

Answer 2

You could use the sql_dependencies view:

select OBJECT_NAME(referenced_major_id) as DependantObject
from sys.sql_dependencies
where object_id = object_id('YourViewName')

To recursively retrieve dependencies (f.e., if you select from a view, this would find the tables that the other view references):

with deps (child, parent) as (
    select d.object_id, d.referenced_major_id
    from sys.sql_dependencies d
    where d.object_id = object_id('YourViewName')
    union all
    select d.object_id, d.referenced_major_id
    from sys.sql_dependencies d
    inner join deps on deps.parent = d.object_id
)
select OBJECT_NAME(parent)
from deps

This method is not fool-proof. For example, if you rename an object using sp_rename, its dependencies are not updated.

Answer 3

If this is something you'll be doing often Red Gate SQL Dependency Tracker (no I don't work for them) is a great tool. I think they have a trial period if you want to try it.