Get Database and Server Cross-References

Applicability:

SQL Server 2000: N/A

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Tested

SQL Server 2014: Tested

SQL Server 2016: Tested

SQL Server 2017: Not Tested

Credits:

Author:Phil Factor/ChillyDBA

Date: 5 Jun 2020

Description

Returns a list of all cross-database and cross-server references

Code

Get all Database and Server Cross-References:

SELECT

@@SERVERNAME AS Referencing_Server

,DB_NAME() AS Referencing_Database

,so.Type_Desc AS Referencing_Object_Type

,OBJECT_SCHEMA_NAME(so.object_id) AS Referencing_Object_Schema

,so.name AS Referencing_Object_Name

,Referenced_Server_Name AS Referenced_Server

,Referenced_Database_Name AS Referenced_Database

,Referenced_Schema_Name AS Referenced_Object_Schema

,Referenced_Entity_Name AS Referenced_Object_Name

,CASE

WHEN Referenced_Database_Name IS NULL THEN 0

ELSE 1

END AS Is_Cross_Database_Reference


,CASE

WHEN Referenced_Server_Name IS NULL THEN 0

ELSE 1

END AS Is_Cross_Server_Reference


FROM sys.sql_expression_dependencies sed

INNER JOIN sys.objects so

ON sed.referencing_id = so.object_id

WHERE is_ambiguous = 0;




--select * from sys.objects


--select * FROM sys.sql_expression_dependencies

--where referenced_id is null