SQL数据库是否有类似于“代码覆盖率”的功能？

Question

我有一个数据库，其中有许多已使用的表格和许多不再使用的表格。虽然我可以手动整理每个表格以查看它们是否仍在使用，但那将是一项繁琐的任务。是否有任何可用于 SQL Server/Oracle 数据库的软件/隐藏功能，可以返回诸如“表 x、y、z 在过去一个月内未被使用”、“表 a、b、c 今天已经被使用了17次”之类的信息？或者可能按“上次修改/选择日期”对表格进行排序的方法？

还有更好的方法吗？谢谢

编辑：在执行“SELECT * FROM sys.tables ORDER BY modify_date desc”时，我发现了一个“modify_date”列，但这似乎只跟踪表结构的修改而非其内容。

- tom

Answer 1

将spt_values替换为您感兴趣的表名，该查询将显示它最后一次被使用的时间以及使用它的内容。

来源：在SQL Server 2005和2008中查找表在Ad Hoc或Procedure调用中被使用的次数

SELECT * FROM(SELECT COALESCE(OBJECT_NAME(s2.objectid),'Ad-Hoc') AS ProcName,execution_count,
    (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,
       last_execution_time
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 ) x
WHERE sql_statement like '%spt_values%'  -- replace here
AND sql_statement NOT like 'SELECT * FROM(SELECT coalesce(object_name(s2.objectid)%'
ORDER BY execution_count DESC

请记住，如果您重新启动盒子，这将被清除。

- SQLMenace

---

这个重置是在分离/重新连接或服务器重启时发生的吗？ - JNK

是的，如果您重新启动盒子，它将被重置。 - SQLMenace

抱歉，我不确定您所说的重新启动盒子是什么意思。是重新启动服务器吗？ - tom

不错。我开始回答“不”，所以这是很好的信息。 - JNK

谢谢，这节省了我好几个小时。 - tom

Answer 2

在Oracle中，您可以使用ASH（Active Session History）查找有关使用的SQL的信息。您还可以使用分层分析器执行代码覆盖测试，从而找到存储过程的哪些部分被使用或未被使用。

如果您想了解表数据的更新情况，您还可以使用DBA_TAB_MODIFICATIONS。这显示了对表或表分区进行了多少次插入、更新和删除操作。一旦生成新的对象统计信息，指定表的行将从DBA_TAB_MODIFICATIONS中删除。您仍然可以在表统计历史记录中获得帮助。这不会显示仅查询的表的任何内容。如果您确实需要了解此信息，则需要使用ASH。

请注意，对于ASH和统计历史记录访问，您确实需要诊断或调整包许可证。（通常您都需要这个）。

- user123664

Answer 3

以下是一些可能对您有用的查询。

select * from sys.dm_db_index_usage_stats

select * from sys.dm_db_index_operational_stats(db_id(),NULL,NULL,NULL)

select * from sys.sql_expression_dependencies /*SQL Server 2008 only*/

第一个和第二个 DMV 报告的差异在这篇博客文章中解释得很好。

- Martin Smith

Answer 4

如果你使用触发器，你可以检测表格的更新、插入或删除。

访问可能更加困难。

- VGE

Answer 5

我使用元数据中的静态分析来确定没有依赖关系的表/列，同时在SQL Server中使用运行时跟踪来查看正在发生的活动。

- Cade Roux

---

你能通过这种方式获取准确的跨数据库依赖信息吗？ - JNK

@JNK 不行 - 你受到数据库边界的限制。就像任何“客户端”代码一样。只有跟踪才能真正告诉你是否可能尝试某些操作。 - Cade Roux

对于 SQL Server 2008，sys.sql_expression_dependencies 包含了引用数据库和服务器名称的列。 - Martin Smith

Answer 6

Ed Elliott的开源工具SQL Cover是一个不错的选择，它内置了流行的单元测试工具tSQLt的支持。

- David Atkinson