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;





Comments