1

Goal:
Display detailed information if database A use database B:s table and its column. if yes, the list will display what is the name of the database B:s table and column that is being used in database A.

Purpose:
Database A has about 200 View and 400 tables and database B has about 154 View and 165 tables and I don't wanna review each table and view in order to locate what table and column it use from database B. The main purpose is to reduce the time.

Problem:
I do not know how to do it?

Information:
*I'm using SQL server 2008 R2

0
2

The catalog view sys.sql_expression_dependencies gets you close. For example:

SELECT 
  [A view] = s.name + '.' + v.name, 'references',
  [B object] = d.referenced_database_name + '.' 
             + d.referenced_schema_name + '.' 
             + d.referenced_entity_name
FROM DatabaseA.sys.views AS v
INNER JOIN DatabaseA.sys.schemas AS s
ON v.[schema_id] = s.[schema_id]
INNER JOIN DatabaseA.sys.sql_expression_dependencies AS d
ON v.[object_id] = d.[referencing_id]
WHERE d.referenced_database_name = N'DatabaseB';

It does not provide column names, however.

Unfortunately other catalog views like sql_dependencies are unreliable for this kind of thing, since they don't follow dependencies outside the scope of the current database. And it's deprecated anyway. However there are more reliable views, like sys.dm_sql_referenced_entities:

SELECT 
  [A view] = s.name + '.' + v.name, 'references',
  [B column] = r.referenced_database_name + '.'
             + r.referenced_schema_name + '.'
             + r.referenced_entity_name + '.'
             + r.referenced_minor_name         
FROM DatabaseA.sys.views AS v
INNER JOIN DatabaseA.sys.schemas AS s
ON v.[schema_id] = s.[schema_id]
CROSS APPLY DatabaseA.sys.dm_sql_referenced_entities
(
  QUOTENAME(s.name) + '.' + QUOTENAME(v.name), N'OBJECT'
) AS r
WHERE r.referenced_minor_id > 0
AND r.referenced_database_name = N'DatabaseB';

Some other links that might help:

Also understand that dependencies are hard, they've changed the approach several times since SQL Server 2000, and we've had a long history of employing workarounds to ensure that the information is current and consistent (but still tend to fail on a regular basis).

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.