INFORMATION_SCHEMA.VIEW_TABLE_USAGE only shows tables from the same catalog

by Metaphor   Last Updated August 13, 2019 19:06 PM - source

I'm working with a database that has many cross-database views. While INFORMATION_SCHEMA.VIEWS lists all these views, INFORMATION_SCHEMA.VIEW_TABLE_USAGE and INFORMATION_SCHEMA.VIEW_COLUMN_USAGE do not.

All views in question are in this format:

CREATE VIEW [dbo].[Invoice]
AS SELECT * FROM [otherdb].[dbo].[Invoice]

The following query returns 0 rows:

SELECT
    VIEW_CATALOG
    ,VIEW_SCHEMA
    ,VIEW_NAME
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE TABLE_CATALOG <> VIEW_CATALOG

Leaving the appropriateness of SELECT * for another discussion, why are the external database references not in the INFORMATION_SCHEMA views and is there any way to get them?



Answers 1


Yes, stop using INFORMATION_SCHEMA for various reasons, including one I didn't mention in that post (yet), which is the fact that they don't handle cross-database dependencies.

Use the catalog views and dynamic management functions instead.

To get just the views, you can use sys.sql_expression_dependencies:

SELECT 
  [schema] = s.name, 
  [view]   = v.name, 
  d.referenced_database_name, 
  d.referenced_schema_name,
  d.referenced_entity_name
FROM sys.views AS v
INNER JOIN sys.schemas AS s
ON v.[schema_id] = s.[schema_id]
INNER JOIN sys.sql_expression_dependencies AS d
ON v.[object_id] = d.referencing_id
WHERE d.referenced_database_name IS NOT NULL;

To get the column names too, you can use sys.dm_sql_referenced_entities:

SELECT 
  [schema] = s.name, 
  [view]   = v.name, 
  r.referenced_database_name,
  r.referenced_schema_name,
  r.referenced_entity_name,
  [column] = r.referenced_minor_name
FROM sys.views AS v
INNER JOIN sys.schemas AS s
ON v.[schema_id] = s.[schema_id]
CROSS APPLY sys.dm_sql_referenced_entities
(
   QUOTENAME(s.name) + N'.' + QUOTENAME(v.name), N'OBJECT'
) AS r
WHERE r.referenced_database_name IS NOT NULL
AND r.referenced_minor_name IS NOT NULL;
Aaron Bertrand
Aaron Bertrand
November 18, 2015 19:44 PM

Related Questions



Why information_schema.columns is_nullable is varchar?

Updated February 11, 2019 21:06 PM