获取SQL Server跨数据库依赖关系

admin

获取SQL Server跨数据库依赖关系

sql

SQL Server版本-2008 R2

我正在评估DMS解决方案，目的是接管维护工作。原始解决方案具有一个中央数据库，该数据库具有与制造商有关的数据。每个经销商还有一个数据库，这意味着存在很多跨数据库依赖项。

问题：

没有数据库文档
没有代码注释
很多堆
没有标准的对象命名约定
除了其他对象之外，中央数据库还具有460多个表和900多个SProcs
每个经销商数据库除了其他对象外，还具有370多个表和2350多个SProcs

第一步，我建议您彻底清理数据库，这对于了解对象依赖关系（包括跨数据库依赖关系）至关重要。我尝试使用Red
Gate的解决方案，但输出的内容太多。我想要的只是数据库中没有任何依赖关系的对象列表-它们既不依赖于其他对象，也没有依赖于这些对象的任何对象。

这是我用来获取依赖项列表的脚本：

SELECT
DB_NAME() referencing_database_name,
OBJECT_NAME (referencing_id) referencing_entity_name,
ISNULL(referenced_schema_name,'dbo') referenced_schema_name,
referenced_entity_name,
ao.type_desc referenced_entity_type,
ISNULL(referenced_database_name,DB_NAME()) referenced_database_name
FROM sys.sql_expression_dependencies sed
JOIN sys.all_objects ao
ON sed.referenced_entity_name = ao.name

我将创建一个表-Dependencies-
将每个数据库的结果集插入到该表中。下一步，我还将创建另一个表AllObjects，该表将包含数据库中所有对象的列表。这是执行此操作的脚本：

SELECT
DB_NAME() DBName,
name,
type_desc
FROM sys.all_objects
WHERE type_desc IN
(
'VIEW',
'SQL_TABLE_VALUED_FUNCTION',
'SQL_STORED_PROCEDURE',
'SQL_INLINE_TABLE_VALUED_FUNCTION',
'USER_TABLE',
'SQL_SCALAR_FUNCTION'
)

现在，此表中没有出现在依赖关系表的referenced_entity_name列中的名称列表应提供我要查找的对象列表。

SELECT
AO.DBName,
AO.name,
AO.type_desc
FROM AllObjects AO
LEFT OUTER JOIN Dependencies D ON
D.referenced_database_name = AO.DBName AND
D.referenced_entity_name = AO.name AND
D.referenced_entity_type = AO.type_desc
WHERE 
D.referenced_database_name IS NULL AND
D.referenced_entity_name IS NULL AND
D.referenced_entity_type IS NULL

现在的问题：

在输出中似乎缺少一些对象依赖性。我想念什么？
如何验证我的发现是正确的？
我的意思是有另一种方式可以做到这一点，所以我可以比较结果并仔细检查吗？

提前致谢，

拉吉

阅读 227

2021-05-10

共1个答案

admin

您可以将结果与以下脚本找到的结果进行比较。下面是完整的文章

CREATE 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);

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)
);

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';

    EXEC(@sql);

    DELETE FROM #databases WHERE database_id = @database_id;
END;

SET NOCOUNT OFF;

SELECT * FROM #dependencies;

2021-05-10