如何查找哪些列没有任何数据(所有值均为 NULL)?
我的数据库中有几个表。我想找到哪些列(在哪些表中)没有任何值(列中全部为 NULL)。在下面的示例中,结果应该是
TestTable1 --> Var2
TestTable2 --> Variable1
我不知道如何创建这种查询。非常感谢您的帮助!
--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
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
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(6)
更新了......好吧,我对此感到非常有趣
Proc 接受两个参数,即要搜索的表和要搜索的表。应用标准。您可以将本质上和 where 子句传递给第二个参数。我编写了将双引号解释回单引号的过程......这也是基于原始开发人员概念构建的。
根据以下版权修改的原始代码......仅使用部分。
Updated....Okay I had way too much fun with this
THe Proc accepts two parameters, the table to search & the Criteria to apply. you can pass essentially and where clause to the second parameter. I wrote the proc to interpret double quotes back to single quotes....again this was built off of the original developers concepts.
Original code modified from copyright below....only using portions.
这太有用了,不能不放入一个方便的小系统过程中 - 特别是如果您刚刚继承了遗留数据库,并且想知道可以删除或忽略哪些列。
This is way too useful not to be put into a handy little system proc - especially if you've just inherited a legacy database, and are wondering what columns you can drop or ignore.
下面是一个 bash 脚本,对于 SQLite 数据库中的所有非空表(或此类数据库中的所有指定表),标识全 NULL 列。假设您选择的编程语言可以与 SQLite 数据库通信,则可以在您选择的编程语言中使用相同的技术。
Here's a bash script that, for all non-empty tables in an SQLite database (or for all the specified tables in such a database), identifies the all-NULL columns. The same technique can be used in the programming language of your choice assuming it can talk to an SQLite database.
对于单个列,
count(ColumnName)
返回ColumName
不为 null 的行数:您可以为所有列生成查询。根据 Martin 的建议,您可以使用
is_nullable = 1
排除不能为 null 的列。例如:如果表数量很大,可以用类似的方式生成所有表的查询。所有表的列表位于 sys.tables 中。
For a single column,
count(ColumnName)
returns the number of rows whereColumName
is not null: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: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
.这是我编写的用于执行相同操作的脚本,它是一个两步手动过程:
UNION ALL
语句。它看起来像这样:Here's a script I wrote to do the same thing, it's a two-step manual process:
UNION ALL
statement. It will look like this: