cross database dependency check

1CREATE PROCEDURE [dbo].[get_crossdatabase_dependencies] AS
2
3SET NOCOUNT ON;
4
5CREATE TABLE #databases(
6    database_id int, 
7    database_name sysname
8);
9
10-- ignore systems databases
11INSERT INTO #databases(database_id, database_name)
12SELECT database_id, name FROM sys.databases
13WHERE database_id > 4;	
14
15DECLARE 
16    @database_id int, 
17    @database_name sysname, 
18    @sql varchar(max);
19
20CREATE TABLE #dependencies(
21    referencing_database varchar(max),
22    referencing_schema varchar(max),
23    referencing_object_name varchar(max),
24    referenced_server varchar(max),
25    referenced_database varchar(max),
26    referenced_schema varchar(max),
27    referenced_object_name varchar(max)
28);
29
30WHILE (SELECT COUNT(*) FROM #databases) > 0 BEGIN
31    SELECT TOP 1 @database_id = database_id, 
32                 @database_name = database_name 
33    FROM #databases;
34
35    SET @sql = 'INSERT INTO #dependencies select 
36        DB_NAME(' + convert(varchar,@database_id) + '), 
37        OBJECT_SCHEMA_NAME(referencing_id,' 
38            + convert(varchar,@database_id) +'), 
39        OBJECT_NAME(referencing_id,' + convert(varchar,@database_id) + '), 
40        referenced_server_name,
41        ISNULL(referenced_database_name, db_name(' 
42             + convert(varchar,@database_id) + ')),
43        referenced_schema_name,
44        referenced_entity_name
45    FROM ' + quotename(@database_name) + '.sys.sql_expression_dependencies';
46
47    EXEC(@sql);
48
49    DELETE FROM #databases WHERE database_id = @database_id;
50END;
51
52SET NOCOUNT OFF;
53
54SELECT * FROM #dependencies;