Facility9

Ever want to know how many queries are referring to other databases on a server? How about a different server?

Worry no more! I have a query that will help you answer these pesky questions:

SELECT  DB_NAME() AS current_db_name,
        OBJECT_NAME(referencing_id) AS o_name ,
        UPPER(COALESCE(sed.referenced_server_name, '')) AS referenced_server_name,
        sed.referenced_database_name,
        sed.referenced_schema_name,
        sed.referenced_entity_name
FROM    sys.sql_expression_dependencies AS sed
WHERE   referenced_database_name <> DB_NAME()
        AND referenced_database_name <> 'msdb'
ORDER BY UPPER(referenced_server_name) ;

Want to run it across every database on your server? We got that, too.

DECLARE @command AS NVARCHAR(MAX);

SET @command = 'USE ?;

SELECT  DB_NAME() AS current_db_name,
        OBJECT_NAME(referencing_id) AS o_name ,
        UPPER(COALESCE(sed.referenced_server_name, '''')) AS referenced_server_name,
        sed.referenced_database_name,
        sed.referenced_schema_name,
        sed.referenced_entity_name
FROM    sys.sql_expression_dependencies AS sed
WHERE   referenced_database_name <> DB_NAME()
        AND referenced_database_name <> ''msdb''
ORDER BY UPPER(referenced_server_name) ;'

EXEC sys.sp_MSforeachdb @command1 = @command

Update: This only applies to SQL Server 2008. Nothing else. Thanks to an astute reader I have been corrected. Now we’re all smarter. Thanks, John.