How to Get list of views with underlying Tables along with their owner information

Question

I would like to get the list of all views in a database with their underlying tables. The underlying tables can be from other databases. So, I would like to know the database names of those tables as well. So, can anyone please help?

I tried below things :

SELECT * FROM [INFORMATION_SCHEMA].[VIEW_TABLE_USAGE]

But, it is not showing any database information of the tables.

I would also like know the owner information of those tables and views present in the database. So, can anyone please help?

score 1 · Answer 1 · answered Dec 12 '17 at 17:16

Information on dependencies are available via the dependency functions and views

here's an example using the sys.sql_expression_dependencies:

SELECT DISTINCT 
    s.name AS view_schema_name,
    v.name AS view_name,
    dp.name AS [owner],
    d.referenced_entity_name,
    d.referenced_database_name,
    d.referenced_server_name
FROM 
    sys.sql_expression_dependencies d
INNER JOIN
    sys.views v ON d.referencing_id = v.object_id
INNER JOIN
    sys.schemas s ON v.schema_id = s.schema_id
INNER JOIN
    sys.database_principals dp ON s.principal_id = dp.principal_id

score 0 · Answer 2 · answered Dec 12 '17 at 17:06

This query gets the tables for the views ([VIEW_CATALOG] is the db):

SELECT [VIEW_CATALOG] AS [database]
       , *
FROM   [INFORMATION_SCHEMA].[VIEW_TABLE_USAGE] AS [VIEW_TABLE_USAGE]
       LEFT JOIN [sys].[tables] AS [tables]
              ON [tables].[name] = [VIEW_TABLE_USAGE].[TABLE_NAME]
                 AND object_schema_name([tables].[object_id]) = [VIEW_TABLE_USAGE].[TABLE_SCHEMA];

This topic tells you how to get the owner information: List table names, owner, schema and columns in SQL server database

How to Get list of views with underlying Tables along with their owner information

2 Answers2