Sql-server – Display a List of used Table and Column

sql-server-2008-r2

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

Best Answer

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).

Related Question