获取 SQL Server 跨数据库依赖项 [英] Getting SQL Server Cross database Dependencies

问题描述

SQL Server 版本 - 2008 R2

SQL Server Version - 2008 R2

我正在评估 DMS 解决方案，目的是接管维护工作.最初的解决方案有一个中央数据库，其中包含与制造商有关的数据.它还为每个经销商提供一个数据库，这意味着存在大量跨数据库依赖关系.

I am working on evaluating a DMS solution, with an objective of taking over maintenance. The original solution has one central database, that has data pertaining to the manufacturer. It also has one database for each dealer, which means there are a lot of cross database dependencies.

问题:

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

作为第一步，我建议彻底清理数据库，因此了解对象依赖关系(包括跨数据库依赖关系)至关重要.我尝试使用 Red Gate 的解决方案，但输出量太大了.我想要的只是数据库中没有任何依赖关系的对象列表 - 它们既不依赖于其他对象，也没有任何依赖于它们的对象.

As a first step, I am recommending a complete clean-up of the DB, for which it is critical to understand object dependencies, including cross database dependencies. I tried using Red Gate's solution, but the output is way too voluminous. All I want is a list of objects in the databases that do not have any dependencies - they neither depend on other objects, nor are there any objects that depend on them.

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

Here is the script I have used to get a list of dependencies:

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 

我将创建一个表 - 依赖项 - 我将从每个数据库中插入这个结果集.作为下一步，我还将创建另一个表 - AllObjects - 它将包含数据库中所有对象的列表.这是执行此操作的脚本:

I will be creating a table - Dependencies - into which I will be inserting this result set from each DB. As a next step, I will also be creating another table - AllObjects- which will contain a list of all objects in the Databases. Here is the script to do this:

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 列中)应提供我要查找的对象列表.

Now, a list of name from this table, that do not appear in the referenced_entity_name column in the dependencies table should give a list of objects that I am looking for.

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

现在问题:

1. 输出中似乎缺少某些对象依赖项.我是什么不见了?
2. 如何验证我的发现是正确的?
3. 我的意思是有没有不同的方法来做到这一点，所以我可以比较结果并仔细检查?

提前致谢，

拉杰

推荐答案

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

You can compare your results to the ones that the following script finds. Here is the full article

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;

这篇关于获取 SQL Server 跨数据库依赖项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！