Finding Cross-Database Dependencies

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