如何跟踪数据库依赖关系？

随着内部应用程序数年的发展，您偶尔会发现很多表，人们认为这些表不再相关并且想要删除。在SQL环境中，甚至可能在SSIS之类的东西中，用于识别数据库依赖关系的实用方法是什么？

我已经在一些相当残酷的地方工作过，例如：

首先拖放，以后再提问（如果数据仓库构建尝试提取不再存在的表，可以终止该数据仓库的构建）
先删除权限，然后等待错误报告（如果无法正确处理故障，可能会导致无声的错误）

我很欣赏SQL Server附带的工具来跟踪该实例内的依赖关系，但是如果您在不同实例上拥有数据库，这些工具似乎会很费劲。是否有使查询依存关系更容易的选项，也许回答诸如“此列在哪里使用”之类的问题？诸如“在此存储过程中在此另一台服务器上结束”或“在此SSIS程序包中在此之上”的答案？

#1 楼

没有简单的方法可以做到这一点。触发器不起作用，就像您从表中选择一样，不会触发任何触发器。我发现最好的方法是让开发人员跟踪他们使用的内容。当要删除某些内容时，请与所有开发团队进行检查，并在所有人退出后重新命名对象。然后一个月都没有破裂，可以放下物体。

#2 楼

搜索sys.sql_modules.definition的使用代码：是否已引用？然后...
检查权限：哪些客户端代码可以调用它？然后...
Profiler

因此：

对于没有引用和权限的表，将不使用它。
在没有引用和某些权限的情况下，运行探查器以查看用法
在没有许可和参考的情况下，添加使用情况的日志记录

我之前所做的就是使表成为视图遮罩该表会导致视图性能下降：（交叉连接本身，是不同的）。您实际上并没有将其删除，但确实会导致客户端超时或投诉...

#3 楼

我过去使用的一种快速方法（它实际上取决于表的大小，索引性能的数量等）是添加一个触发器，该触发器在对表执行操作时记录时间戳。正如我已经说过的那样，这可能会导致性能问题，因此需要谨慎对待-还要注意您的日志记录表不使用标识字段，因为这会弄乱一些使用@@ IDENTITY的旧代码。当然，这可能只是表明应用程序中的某个功能已经使用了一段时间。

当所有可能击中数据库的代码不在数据库中时，很难跟踪依赖关系随机客户端查询数据库。

编辑：
要解决表不能具有SELECT触发器的问题，这是另一种选择，它假设表具有索引（在2008年测试仅）。

SELECT          
    last_user_seek,
    last_user_scan,
    last_user_lookup,
    last_user_update
FROM
    sys.dm_db_index_usage_stats AS usage_stats
INNER JOIN
sys.tables AS tables ON tables.object_id = usage_stats.object_id
WHERE
    database_id = DB_ID() AND
    tables.name = 'mytable'

，但请注意，在服务器重新启动，分离等情况下会清除使用情况统计表。因此，您需要设置一个作业来收集数据。我知道一点技巧。

#4 楼

我过去使用的一种方法是建立要删除的表的候选列表，然后重命名它们并查找故障。

我如何建立列表的方法是：

查看哪些表在当前存储过程，触发器和函数中未使用
空表（零记录）；
未引用表（没有任何关系的表）；
查看自从启动DB Server（DMV）以来未使用哪些表

在文本文件中构建列表后，我制作了一个批处理脚本来解析我们的.cs文件（我们只有本地映射版本控制文件夹中的.net项目），然后查看这些表是否在.cs文件中使用（应该不会发生，但是，嘿。我感到惊讶）。如果不是，那么很明显，如果是，那么我们将建立一个列表，并让开发人员检查该模块是否仍在使用中。

因此，简而言之，以前的人是对的，没有银弹。

#5 楼

我在公司实施的策略是将所有与SQL Server接触的内容置于源代码控制下的中央位置。

asp.net项目
SSRS项目
SSIS项目
我甚至将所有
数据库对象脚本编写为
的存储库/> sorts。

我还没有设置它，但是最终我想要实现某种索引/中央搜索机制，可以用来搜索特定的表，存储过程等。我们实际上是一个新的SQL Server商店-从FoxPro转换而来。因此，旧的SQL对象不是什么大问题，但是我正在为将来做计划。

我发现重命名/跟踪方法存在的问题是某些事情仅每年运行一次，甚至没有每年。更不用说人们要求您写的各种特别内容，然后在几个月或几年后再询问一次。

#6 楼

有多种工具和技术可用于跟踪依赖项，包括：

我知道的工具：

SQL Server依赖关系查看器（但可能有问题）如果sp使用表是在创建表之前创建的）

Redgate SQL依赖项跟踪器（通过@Eric Humphrey的回答）

Resharper（可用于查看的.net工具调用路径，我认为它可以用来跟踪使用关键SQL调用的位置）

方法

代码搜索以使用SQL对象（复制了一些查看上面的工具）

查看使用情况统计信息（即：何时最后一次调用SQL对象），我使用以下SQL：

SELECT 
    last_execution_time,   
    (SELECT TOP 1 
        SUBSTRING(s2.text,statement_start_offset / 2+1 , 
            ((CASE WHEN statement_end_offset = -1 THEN 
                (LEN(CONVERT(nvarchar(max),s2.text)) * 2) 
            ELSE statement_end_offset END) - statement_start_offset) / 2+1)
    )  AS sql_statement,
    execution_count
FROM sys.dm_exec_query_stats AS s1 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2  
WHERE 
    s2.text like '%[OBJECT NAME]%' 
    and last_execution_time > [DATE YOU CARE ABOUT]
ORDER BY last_execution_time desc

注意：重新启动服务器，分离服务器等时，使用情况统计信息表将被清除。因此，您需要设置一个作业来收集数据。我知道一点技巧。（来自@Miles D）

技术

搜索上次使用情况（请参见上述使用情况统计信息）
搜索其使用位置（请参见工具）
与开发人员一起检查代码使用情况（通过@MrDenny）
重命名对象（即：使用_toBeDropped发布/前缀）并注意错误
更改权限并注意错误
删除对象祈祷

#7 楼

几年前，我试图构建一个工具来检查类似的东西。 TL; DR的答案是我发现当时无法使用可用资源。

该列在哪里使用？

当您意识到许多查询，视图和存储过程使用该列所在的表中的select *时，此问题将变得更加复杂。然后，您需要查看那些使用这些结果-因此，您需要一些能够读取源代码的扫描器/索引器/解析器，这些源代码可能是C＃，Delphi，Java，VB，ASP（经典）等，以便尝试查找对该列的每个引用。然后，您需要分析这些程序，以尝试确定该代码是否还要被调用。

#8 楼

不会处理超出SQL引用的限制，但您可能想签出Redgate的SQL依赖跟踪器。这是一个不错的可视化工具。

#9 楼

这并不是您问题的真正答案，但我认为值得一提：这是数据库之外的所有系统都应通过视图和存储库进行通信的原因之一。您可以在可搜索的.sql文件中找到这些脚本的生成脚本，因此可以轻松查看外部是否正在使用特定的表或列。现在对您的需求并没有太大帮助。但是，当开发人员连接到您的数据库并抱怨必须等待您（或充当DBA的任何人）提出他们所需的视图和存储库时，您可以告诉他们：“任何表或列都可以删除或重命名。 m仅负责使您了解视图和存储库的更改。”他们只需要对这些特定的变化进行回归测试。

#10 楼

可以使用以下TSQL sys.dm_sql_referencing_entities或sys.sql_expression_dependencies

另外，例如SQL Negotiator Pro，Redgate等工具也可以使用GUI直观地为您生成此信息

编程黑洞网