如何查找哪些列没有任何数据(所有值均为 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屋!