[스크립트] DBtoDB 참조관계 조회

--https://stackoverflow.com/questions/13757387/getting-sql-server-cross-database-dependencies 변형
alter PROCEDURE [dbo].[get_crossdatabase_dependencies] AS

SET NOCOUNT ON;

CREATE TABLE #databases(
    database_id int, 
    database_name sysname
);

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 */

DECLARE 
    @database_id int, 
    @database_name sysname, 
    @sql varchar(max), 
    @sql2 varchar(max);

CREATE TABLE #dependencies(
    referencing_database varchar(max),
    referencing_schema varchar(max),
    referencing_object_name varchar(max),
    referenced_server varchar(max),
    referenced_database varchar(max),
    referenced_schema varchar(max),
    referenced_object_name varchar(max)
);
CREATE TABLE #objtype(
    database_name varchar(max),
    obj_name varchar(max),
    obj_type varchar(100)
);


WHILE (SELECT COUNT(*) FROM #databases) > 0 BEGIN
    SELECT TOP 1 @database_id = database_id, 
                 @database_name = database_name 
    FROM #databases;

    SET @sql = 'INSERT INTO #dependencies select 
        DB_NAME(' + convert(varchar,@database_id) + '), 
        OBJECT_SCHEMA_NAME(referencing_id,' 
            + convert(varchar,@database_id) +'), 
        OBJECT_NAME(referencing_id,' + convert(varchar,@database_id) + '), 
        referenced_server_name,
        ISNULL(referenced_database_name, db_name(' 
             + convert(varchar,@database_id) + ')),
        referenced_schema_name,
        referenced_entity_name
    FROM ' + quotename(@database_name) + '.sys.sql_expression_dependencies'
    
	set @sql2= 'insert into #objtype select DB_NAME(' 
		+ convert(varchar,@database_id) + '),name,type_desc from '
		+ quotename(@database_name) + '.sys.objects where type_desc 
		in (''SQL_SCALAR_FUNCTION'',''SQL_STORED_PROCEDURE'',''SQL_TABLE_VALUED_FUNCTION'',''USER_TABLE'',''VIEW'')'

    EXEC(@sql);
	EXEC(@sql2);
    DELETE FROM #databases WHERE database_id = @database_id;
END;

SET NOCOUNT OFF;

SELECT  a.referencing_database , a.referencing_schema , a.referencing_object_name , b.obj_type, a.referenced_server , a.referenced_database ,  a.referenced_schema , a.referenced_object_name , C.obj_type FROM #dependencies A
left join  #objtype B on a.referencing_database=b.database_name and a.referencing_object_name=b.obj_name
left join  #objtype C on a.referenced_database=C.database_name and a.referenced_object_name=C.obj_name
where a.referenced_database !=a.referencing_database and referenced_server is NULL
 /*
 SELECT * FROM #dependencies A
SELECT * FROM  #objtype B 

SELECT  a.referencing_database , a.referencing_schema , a.referencing_object_name , b.obj_type, a.referenced_server , a.referenced_database ,  a.referenced_schema , a.referenced_object_name , C.obj_type FROM #dependencies A
left join  #objtype B on a.referencing_database=b.database_name and a.referencing_object_name=b.obj_name
left join  #objtype C on a.referenced_database=C.database_name and a.referenced_object_name=C.obj_name
  */