Search Objects with Server References in SQL

Use this SQL script to search for objects with server references within the source code.

DECLARE
    @dsql nvarchar(MAX) = N'',
    @debug bit = 0;
       
SELECT TOP (10000)           
    @dsql = CONCAT(@dsql, N'UNION ALL
   
SELECT             
    database_id = ', d.database_id, N',       
    database_name = ''', d.name, N''',
    object_name = CONCAT(s.name, N''.'', o.name) COLLATE SQL_Latin1_General_CP1_CS_AS,
    o.type,
    sed.referenced_class,
    sed.referenced_class_desc,
    sed.referenced_server_name COLLATE SQL_Latin1_General_CP1_CS_AS,
    sed.referenced_database_name COLLATE SQL_Latin1_General_CP1_CS_AS,
    sed.referenced_schema_name COLLATE SQL_Latin1_General_CP1_CS_AS,
    sed.referenced_entity_name COLLATE SQL_Latin1_General_CP1_CS_AS
FROM
    ', QUOTENAME(d.name), N'.sys.sql_expression_dependencies sed
    JOIN ', QUOTENAME(d.name), N'.sys.objects o
        ON sed.referencing_id = o.object_id
    JOIN ', QUOTENAME(d.name), N'.sys.schemas s
        ON o.schema_id = s.schema_id
WHERE
    sed.referenced_server_name LIKE N''%''   
'           
    )       
FROM           
    sys.databases d       
WHERE            
    d.state = 0        /* ONLINE */
    AND d.database_id > 4    /* exclude system databases */
ORDER BY            
    d.name;   
           
SET @dsql = STUFF(@dsql, 1, 13, N'') + N';';           
           
IF @debug = 1           
BEGIN EXEC dbo.sp_LongPrint @_string = @dsql; END;           
ELSE            
BEGIN EXEC sys.sp_executesql @dsql; END;

We're available if you need assistance implementing custom SQL code. Contact us for help!