查找未使用的字段(全为空) [英] Find fields that aren't used (have all nulls)

问题描述

我继承了一个遗留数据库，许多字段不再使用.我可以通过查询过去几年并查看哪些字段为空来判断，至少可以获得我可能查看的位置的基线.

I've inherited a legacy database, and many, many fields are no longer being used. I can tell by querying on the last couple of years and seeing which fields are null, to at least get a baseline for where I might look.

是否有一种优雅的方法可以找出数据库中给定集合中所有记录的哪些字段为空?

Is there an elegant way to figure out which fields in the database are null for all records in a given set?

推荐答案

DECLARE @table NVARCHAR(512);
SET @table = N'dbo.tablename';

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = N'';

SELECT @sql = @sql + QUOTENAME(name) 
     + ' = SUM(CASE WHEN ' + QUOTENAME(name) + ' IS NULL THEN 1 ELSE 0 END),'
  FROM sys.columns
  WHERE object_id = OBJECT_ID(@table)
  AND is_nullable = 1;

SELECT @sql = 'SELECT ' + @sql + ' Total_Count = COUNT(*)
  FROM ' + @table + ';';

EXEC sp_executesql @sql;

任何出现 0 的列都为空(除非 Total_Count 列也出现 0，在这种情况下表为空).请注意，此查询在大型表上的开销会很大.

Any columns that come out 0 have all nulls (unless the Total_Count column also comes out 0, in which case the table is empty). Note that this query will be pretty expensive on a large table.

这篇关于查找未使用的字段(全为空)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！