get_crossdatabase_dependencies.sql

USE [master];
GO

IF OBJECT_ID('dbo.get_crossdatabase_dependencies') IS NULL
    BEGIN
        EXEC ('CREATE PROCEDURE dbo.get_crossdatabase_dependencies AS RETURN 0');
    END;
GO

ALTER PROCEDURE [dbo].[get_crossdatabase_dependencies] 
                @TargetTable VARCHAR(MAX)
AS
    BEGIN
        SET NOCOUNT ON;
        CREATE TABLE #databases ( 
                     database_id   INT , 
                     database_name SYSNAME
                                );
        CREATE TABLE #tbl1 ( 
                     referencing_database_name              VARCHAR(MAX) , 
                     referencing_schema_name                VARCHAR(MAX) , 
                     referencing_entity_name                VARCHAR(MAX) , 
                     referencing_entity_id                  INT , 
                     referencing_entity_type                VARCHAR(MAX) , 
                     referencing_entity_type_desc           VARCHAR(MAX) , 
                     referencing_entity_modify_date         DATETIME , 
                     referencing_entity_last_execution_time DATETIMEOFFSET , 
                     referenced_entity_name                 VARCHAR(MAX)
                           );
        CREATE TABLE #tbl2 ( 
                     referencing_database_name              VARCHAR(MAX) , 
                     referencing_schema_name                VARCHAR(MAX) , 
                     referencing_entity_name                VARCHAR(MAX) , 
                     referencing_entity_id                  INT , 
                     referencing_entity_type                VARCHAR(MAX) , 
                     referencing_entity_type_desc           VARCHAR(MAX) , 
                     referencing_entity_modify_date         DATETIME , 
                     referencing_entity_last_execution_time DATETIMEOFFSET , 
                     referenced_entity_name                 VARCHAR(MAX) , 
                     is_updated                             INT
                           );
        INSERT INTO #databases ( database_id , database_name
                               ) 
               SELECT database_id , [name]
               FROM sys.databases
               WHERE 1 = 1
                     AND 
                     [state] <> 6 /* ignore offline dbs */

                     AND 
                     database_id > 4 /* ignore system dbs */

               ORDER BY [name];
        DECLARE @database_id INT , @database_name SYSNAME , @quoted_database_name VARCHAR(MAX) , @sql VARCHAR(MAX);
        WHILE ( SELECT COUNT(*)
                FROM #databases
              ) > 0
            BEGIN
                SELECT TOP 1 @database_id = database_id , @database_name = database_name , @quoted_database_name = QUOTENAME(DB_NAME(CONVERT(NVARCHAR , database_id)))
                FROM #databases;
                SET @sql = 'insert into #tbl1 select 
					quotename(db_name(' + CONVERT(NVARCHAR , @database_id) + ')),
					quotename(object_schema_name(d.referencing_id, ' + CONVERT(NVARCHAR , @database_id) + ')),
					quotename(object_name(d.referencing_id, ' + CONVERT(NVARCHAR , @database_id) + ')),
					d.referencing_id,
					o.type,
					o.type_desc,
					max(o.modify_date),
					max(q.last_execution_time),
					d.referenced_entity_name
				from ' + @quoted_database_name + '.sys.sql_expression_dependencies d
				left join ' + @quoted_database_name + '.sys.objects o on d.referencing_id = o.object_id
				left join ' + @quoted_database_name + '.sys.query_store_query q on d.referencing_id = q.object_id
				where d.referenced_entity_name = ''' + @TargetTable + '''
				group by	d.referencing_id,
							o.type,
							o.type_desc,
							d.referenced_entity_name';
                EXEC (@sql);
                DELETE FROM #databases
                WHERE database_id = @database_id;
            END;
        SELECT 'Referencing Entity' = t.referencing_database_name + '.' + t.referencing_schema_name + '.' + t.referencing_entity_name , 'Referencing Entity Type' = t.referencing_entity_type_desc
        FROM #tbl1 AS t
        ORDER BY referencing_database_name , referencing_schema_name , referencing_entity_name;
        DECLARE @referencing_database_name VARCHAR(MAX) , @referencing_schema_name VARCHAR(MAX) , @referencing_entity_name VARCHAR(MAX);
        WHILE ( SELECT COUNT(*)
                FROM #tbl1
              ) > 0
            BEGIN
                SELECT TOP 1 @referencing_database_name = referencing_database_name , @referencing_schema_name = referencing_schema_name , @referencing_entity_name = referencing_entity_name
                FROM #tbl1;
                SET @sql = 'insert into #tbl2 select
					d.referencing_database_name,
					d.referencing_schema_name,
					d.referencing_entity_name,
					d.referencing_entity_id,
					d.referencing_entity_type,
					d.referencing_entity_type_desc,
					d.referencing_entity_modify_date,
					d.referencing_entity_last_execution_time,
					d.referenced_entity_name,
					e.is_updated
				from #tbl1 d
				inner join ' + @referencing_database_name + '.sys.dm_sql_referenced_entities(''' + @referencing_schema_name + '.' + @referencing_entity_name + ''', ''OBJECT'') e on d.referenced_entity_name = e.referenced_entity_name
				where d.referencing_database_name = ''' + @referencing_database_name + '''
				and	  d.referencing_schema_name = ''' + @referencing_schema_name + '''
				and   d.referencing_entity_name = ''' + @referencing_entity_name + '''
		        and e.referenced_minor_name is null';
                EXEC (@sql);
                DELETE FROM #tbl1
                WHERE referencing_database_name = @referencing_database_name
                      AND 
                      referencing_schema_name = @referencing_schema_name
                      AND 
                      referencing_entity_name = @referencing_entity_name;
            END;
        SELECT 'Referencing Entity' = t.referencing_database_name + '.' + t.referencing_schema_name + '.' + t.referencing_entity_name , 'Referencing Entity Type' = t.referencing_entity_type_desc , 'Last Modified' = format(t.referencing_entity_modify_date , 'dd/MM/yyyy') , 'Last Executed' = CASE
                                                                                                                                                                                                                                                                                                       WHEN t.referencing_entity_last_execution_time IS NULL
                                                                                                                                                                                                                                                                                                       THEN ''
                                                                                                                                                                                                                                                                                                       ELSE format(t.referencing_entity_last_execution_time , 'dd/MM/yyyy')
                                                                                                                                                                                                                                                                                                   END
        FROM #tbl2 AS t
        WHERE is_updated = 1
        ORDER BY referencing_database_name , referencing_schema_name , referencing_entity_name;
        DROP TABLE #tbl1;
        DROP TABLE #tbl2;
        DROP TABLE #databases;
    END;