2

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?

9

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.