CODEWITHSUNDEEP
sql-serversql-server-2012
Ash Atre
Ash Atre

Reputation: 305

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

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?

Upvotes: 0

Views: 728

Answers (2)

mauridb
mauridb

Reputation: 1569

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

Upvotes: 1

Katherine Elizabeth Lightsey
Katherine Elizabeth Lightsey

Reputation: 853

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

Upvotes: 0

Related Questions