如何查找哪些列没有任何数据(所有值均为 NULL)? [英] How to find which columns don't have any data (all values are NULL)?

问题描述

我在一个数据库中有几个表.我想找到哪些列(在哪些表中)没有任何值(列中全部为 NULL).我下面的例子，结果应该是

I have several tables in a database. I would like to find which columns (in which tables) don't have any values (all NULL in a column). I the example below, the result should be

TestTable1 --> Var2
TestTable2 --> Variable1

我不知道如何创建这种查询.非常感谢您的帮助！

I don't have any idea how to create this kind of query. Your help is most appreciated!

--create first table
create table dbo.TestTable1 (
sur_id int identity(1,1) not null primary key,
var1 int null,
var2 int null
)
go

--insert some values
insert into dbo.TestTable1 (var1) 
    select 1 union all select 2 union all select 3

--create second table
create table dbo.TestTable2 (
sur_id int identity(1,1) not null primary key,
variable1 int null,
variable2 int null
)

--and insert some values
insert into dbo.TestTable2 (variable2) 
    select 1 union all select 2 union all select 3

推荐答案

对于单列，count(ColumnName) 返回ColumName 不为空的行数:

For a single column, count(ColumnName) returns the number of rows where ColumName is not null:

select  count(TheColumn)
from    YourTable

您可以为所有列生成查询.根据 Martin 的建议，您可以使用 is_nullable = 1 排除不能为空的列.例如:

You can generate a query for all columns. Per Martin's suggestion, you can exclude columns that cannot be null with is_nullable = 1. For example:

select  'count(' + name + ') as ' + name + ', '
from    sys.columns
where   object_id = object_id('YourTable')
        and is_nullable = 1

如果表的数量很大，可以用类似的方式生成所有表的查询.所有表的列表在 sys.tables 中.

If the number of tables is large, you can generate a query for all tables in a similiar way. The list of all tables is in sys.tables.

这篇关于如何查找哪些列没有任何数据(所有值均为 NULL)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！