找出表中哪些列从未使用过

Question

我希望的不是代码的答案，而是一种策略。我不得不处理一个包含大量无用信息的数据库，这些信息甚至从未被访问过。我甚至猜测信噪比对噪声有利。我正在寻找一些统计/分析工具来帮助找出哪些表中的哪些列从未被访问过。

不幸的是，大多数时候不使用的是列(而不是表)。所以我需要把它分解到这个级别。我使用SQL Server 2008，如果这很重要的话。

Answer 1

您可以使用audit

use master
GO

create server audit MyAudit
    to file (filepath = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA')
GO

alter server audit MyAudit
    with (state = on);
GO

use [<YourDatabase]
GO

create database audit specification MyDatabaseAudit
for server audit MyAudit
add (
    select on [<YouSchema>].[YourTable] by dbo
)
with (state = on)
GO

select [<YourColumn>] from [<YouSchema>].[YourTable]
GO

use master
GO

alter server audit MyAudit
    with (state = off);
GO

select * from sys.fn_get_audit_file ('C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MyAudit*',default,default);
GO

好吧，这不会让您获得像列一样的粒度，但是因为它捕获了每个select语句，所以您应该能够从它提供的数据中获取这些信息。

显然，上面给出的文件路径只是出于示例的目的。

另一种方法是SQL Profiler跟踪，但是，这也不允许您到达列的级别，而只是捕获正在运行的select语句。这样做的缺点是，您需要正确地对其进行过滤，才能获得您想要的信息，这可能会很麻烦。此外，还可以有更高的开销。

Answer 2

检查这一点的唯一方法是访问正在访问数据库的所有应用程序代码，并查看这些代码是如何在应用程序中使用的。如果您没有像select * from table这样的查询，那么您将会有一个简单的工作。

检查应用程序代码：没有什么比手动检查更能给您提供更精确的概述，但这可能需要很长时间。如果您的数据库访问代码存储在一组类中，并且类与数据库表紧密耦合，那么您可以为每个数据库列“查找所有引用”。

触发器:在所有可疑的表上设置insert、update、delete触发器，然后将所有更新的列存储在一个单独的表中。这将为您提供有关哪些列最常用于更新的详细信息-因此，其他列将成为删除的可疑列。

分析:对可疑的表进行设置分析，运行一段时间并分析日志。

第三方工具：使用这些工具查找数据库中未被任何其他对象引用的所有对象。在识别可疑的表/列时可能很有用。尝试使用SQL Dependancy tracker或ApexSQL Clean

Answer 3

如果您很幸运，并且所有数据都是通过SP访问的，并且没有*，那么您可以尝试使用information_schema.columns表。类似于‘select distinct’

select p.[type]
      ,p.[name]
      ,c.[definition]
  from sys.objects p
  join sys.sql_modules c
    on p.object_id = c.object_id
 where p.[type] = ''P''
   and c.[definition] like %'+COLUMN_NAME+'%' FROM INFORMATION_SCHEMA.COLUMNS

`

需要调整此查询以执行生成的SQL语句。