关于SQL Server：SQL：仅选择具有NULL值的列

SQL: Select columns with NULL values only

如何选择表中所有行仅包含NULL值的所有列？我正在使用MS SQL Server2005。我试图找出表中未使用的列，以便将其删除。

这是sql 2005或更高版本：用您的表名替换ADDR_Address。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

declare @col varchar(255), @cmd varchar(max)

DECLARE getinfo cursor for
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID
WHERE t.Name = 'ADDR_Address'

OPEN getinfo

FETCH NEXT FROM getinfo into @col

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @cmd = 'IF NOT EXISTS (SELECT top 1 * FROM ADDR_Address WHERE [' + @col + '] IS NOT NULL) BEGIN print ''' + @col + ''' end'
EXEC(@cmd)

FETCH NEXT FROM getinfo into @col
END

CLOSE getinfo
DEALLOCATE getinfo

1
2
3

SELECT cols
FROM table
WHERE cols IS NULL

这应该为您提供表" Person "中只有NULL值的所有列的列表。您将获得多个结果集的结果，这些结果集为空或包含单个列的名称。您需要在两个位置替换" Person "才能将其与另一个表一起使用。

1
2
3
4
5
6
7
8
9
10
11

DECLARE crs CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM syscolumns WHERE id=OBJECT_ID('Person')
OPEN crs
DECLARE @name sysname
FETCH NEXT FROM crs INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('SELECT ''' + @name + ''' WHERE NOT EXISTS (SELECT * FROM Person WHERE ' + @name + ' IS NOT NULL)')
FETCH NEXT FROM crs INTO @name
END
CLOSE crs
DEALLOCATE crs

此处是Bryan 2008年及以后版本查询的更新版本。它使用INFORMATION_SCHEMA.COLUMNS，为表架构和表名称添加变量。列数据类型已添加到输出中。查找特定数据类型的列时，包含列数据类型会有所帮助。我没有添加列宽或其他任何内容。

使用RAISERROR ... WITH NOWAIT进行输出，因此文本将立即显示，而不是像PRINT那样一次全部显示(在大多数情况下)。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

SET NOCOUNT ON;

DECLARE
@ColumnName sysname
,@DataType nvarchar(128)
,@cmd nvarchar(max)
,@TableSchema nvarchar(128) = 'dbo'
,@TableName sysname = 'TableName';

DECLARE getinfo CURSOR FOR
SELECT
c.COLUMN_NAME
,c.DATA_TYPE
FROM
INFORMATION_SCHEMA.COLUMNS AS c
WHERE
c.TABLE_SCHEMA = @TableSchema
AND c.TABLE_NAME = @TableName;

OPEN getinfo;

FETCH NEXT FROM getinfo INTO @ColumnName, @DataType;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = N'IF NOT EXISTS (SELECT * FROM ' + @TableSchema + N'.' + @TableName + N' WHERE [' + @ColumnName + N'] IS NOT NULL) RAISERROR(''' + @ColumnName + N' (' + @DataType + N')'', 0, 0) WITH NOWAIT;';
EXECUTE (@cmd);

FETCH NEXT FROM getinfo INTO @ColumnName, @DataType;
END;

CLOSE getinfo;
DEALLOCATE getinfo;

还是只想查看一列是否只有NULL值(因此可能未使用)？

进一步澄清该问题可能会有所帮助。

编辑：
好的..这是一些非常粗糙的代码，可以帮助您开始...

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

SET NOCOUNT ON
DECLARE @TableName Varchar(100)
SET @TableName='YourTableName'
CREATE TABLE #NullColumns (ColumnName Varchar(100), OnlyNulls BIT)
INSERT INTO #NullColumns (ColumnName, OnlyNulls) SELECT c.name, 0 FROM syscolumns c INNER JOIN sysobjects o ON c.id = o.id AND o.name = @TableName AND o.xtype = 'U'
DECLARE @DynamicSQL AS Nvarchar(2000)
DECLARE @ColumnName Varchar(100)
DECLARE @RC INT
SELECT TOP 1 @ColumnName = ColumnName FROM #NullColumns WHERE OnlyNulls=0
WHILE @@ROWCOUNT > 0
BEGIN
SET @RC=0
SET @DynamicSQL = 'SELECT TOP 1 1 As HasNonNulls FROM ' + @TableName + ' (nolock) WHERE ''' + @ColumnName + ''' IS NOT NULL'
EXEC sp_executesql @DynamicSQL
set @RC=@@rowcount
IF @RC=1
BEGIN
SET @DynamicSQL = 'UPDATE #NullColumns SET OnlyNulls=1 WHERE ColumnName=''' + @ColumnName + ''''
EXEC sp_executesql @DynamicSQL
END
ELSE
BEGIN
SET @DynamicSQL = 'DELETE FROM #NullColumns WHERE ColumnName=''' + @ColumnName+ ''''
EXEC sp_executesql @DynamicSQL
END
SELECT TOP 1 @ColumnName = ColumnName FROM #NullColumns WHERE OnlyNulls=0
END

SELECT * FROM #NullColumns

DROP TABLE #NullColumns
SET NOCOUNT OFF

是的，有更简单的方法，但是我现在要开会。祝你好运！

您可以这样做：

1
2
3
4

select
count(<columnName>)
from
<tableName>

如果计数返回0，则表示该列中的所有行都为NULL(或者表中根本没有行)。

可以更改为

1
2
3
4

select
case(count(<columnName>)) when 0 then 'Nulls Only' else 'Some Values' end
from
<tableName>

如果要使其自动化，可以使用系统表来迭代您感兴趣的表中的列名。

实际上不确定2005年，但2008年吃了它：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

USE [DATABASE_NAME] -- !
GO

DECLARE @SQL NVARCHAR(MAX)
DECLARE @TableName VARCHAR(255)

SET @TableName = 'TABLE_NAME' -- !

SELECT @SQL =
(
SELECT
CHAR(10)
+'DELETE FROM ['+t1.TABLE_CATALOG+'].['+t1.TABLE_SCHEMA+'].['+t1.TABLE_NAME+'] WHERE '
+(
SELECT
CASE t2.ORDINAL_POSITION
WHEN (SELECT MIN(t3.ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS t3 WHERE t3.TABLE_NAME=t2.TABLE_NAME) THEN ''
ELSE 'AND '
END
+'['+COLUMN_NAME+'] IS NULL' AS 'data()'
FROM INFORMATION_SCHEMA.COLUMNS t2 WHERE t2.TABLE_NAME=t1.TABLE_NAME FOR XML PATH('')
) AS 'data()'
FROM INFORMATION_SCHEMA.TABLES t1 WHERE t1.TABLE_NAME = @TableName FOR XML PATH('')
)

SELECT @SQL -- EXEC(@SQL)

如果您需要列出所有列值均为NULL的所有行，则可以使用COLLATE函数。这将获取值列表，并返回第一个非空值。如果将所有列名称添加到列表中，然后使用IS NULL，则应该获得仅包含空值的所有行。

1	SELECT * FROM MyTable WHERE COLLATE(Col1, Col2, Col3, Col4......) IS NULL

您实际上不应该有任何表中所有columns为null的表，因为这意味着您没有primary key(不允许为NULL)。没有主键是可以避免的。这会破坏第一个范式。

我还建议搜索所有具有相同值的字段，而不仅仅是NULL。

即，对每个表中的每一列执行查询：

1	SELECT COUNT(DISTINCT field) FROM tableName

专注于结果返回1的那些。

在这里，我为任何类型的SQL表创建了一个脚本。请复制此存储过程并在您的环境中创建该存储过程，然后使用Table运行该存储过程。

1	exec [dbo].[SP_RemoveNullValues] 'Your_Table_Name'

存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82

GO
/****** Object: StoredProcedure [dbo].[SP_RemoveNullValues] Script Date: 09/09/2019 11:26:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- akila liyanaarachchi
Create procedure [dbo].[SP_RemoveNullValues](@PTableName Varchar(50) ) as
begin

DECLARE Cussor CURSOR FOR
SELECT COLUMN_NAME,TABLE_NAME,DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @PTableName

OPEN Cussor;

Declare @ColumnName Varchar(50)
Declare @TableName Varchar(50)
Declare @DataType Varchar(50)
Declare @Flage int

FETCH NEXT FROM Cussor INTO @ColumnName,@TableName,@DataType
WHILE @@FETCH_STATUS = 0
BEGIN

set @Flage=0

If(@DataType in('bigint','numeric','bit','smallint','decimal','smallmoney','int','tinyint','money','float','real'))
begin
set @Flage=1
end
If(@DataType in('date','atetimeoffset','datetime2','smalldatetime','datetime','time'))
begin
set @Flage=2
end
If(@DataType in('char','varchar','text','nchar','nvarchar','ntext'))
begin
set @Flage=3
end

If(@DataType in('binary','varbinary'))
begin
set @Flage=4
end

DECLARE @SQL VARCHAR(MAX)

if (@Flage in(1,4))
begin

SET @SQL =' update ['+@TableName+'] set ['+@ColumnName+']=0 where ['+@ColumnName+'] is null'
end

if (@Flage =3)
begin

SET @SQL =' update ['+@TableName+'] set ['+@ColumnName+'] = '''' where ['+@ColumnName+'] is null '
end

if (@Flage =2)
begin

SET @SQL =' update ['+@TableName+'] set ['+@ColumnName+'] ='+'''1901-01-01 00:00:00.000'''+' where ['+@ColumnName+'] is null '
end

EXEC(@SQL)

FETCH NEXT FROM Cussor INTO @ColumnName,@TableName,@DataType
END

CLOSE Cussor
DEALLOCATE Cussor

END

\\'user2466387 \\'版本的更新版本，带有一个附加的小型测试，可以提高性能，因为它对测试不可为空的列没有用：

1	AND IS_NULLABLE = 'YES'

完整代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

SET NOCOUNT ON;

DECLARE
@ColumnName sysname
,@DataType nvarchar(128)
,@cmd nvarchar(max)
,@TableSchema nvarchar(128) = 'dbo'
,@TableName sysname = 'TableName';

DECLARE getinfo CURSOR FOR
SELECT
c.COLUMN_NAME
,c.DATA_TYPE
FROM
INFORMATION_SCHEMA.COLUMNS AS c
WHERE
c.TABLE_SCHEMA = @TableSchema
AND c.TABLE_NAME = @TableName
AND IS_NULLABLE = 'YES';

OPEN getinfo;

FETCH NEXT FROM getinfo INTO @ColumnName, @DataType;

WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = N'IF NOT EXISTS (SELECT * FROM ' + @TableSchema + N'.' + @TableName + N' WHERE [' + @ColumnName + N'] IS NOT NULL) RAISERROR(''' + @ColumnName + N' (' + @DataType + N')'', 0, 0) WITH NOWAIT;';
EXECUTE (@cmd);

FETCH NEXT FROM getinfo INTO @ColumnName, @DataType;
END;

CLOSE getinfo;
DEALLOCATE getinfo;

1
2
3

SELECT t.column_name
FROM user_tab_columns t
WHERE t.nullable = 'Y' AND t.table_name = 'table name here' AND t.num_distinct = 0;

试试看-

1
2
3
4
5
6
7
8
9
10
11
12

DECLARE @table VARCHAR(100) = 'dbo.table'

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = @sql + 'IF NOT EXISTS(SELECT 1 FROM ' + @table + ' WHERE ' + c.name + ' IS NOT NULL) PRINT ''' + c.name + ''''
FROM sys.objects o
JOIN sys.columns c ON o.[object_id] = c.[object_id]
WHERE o.[type] = 'U'
AND o.[object_id] = OBJECT_ID(@table)
AND c.is_nullable = 1

EXEC(@sql)

您将不得不遍历一组列并检查每个列。您应该能够使用DESCRIBE table命令获取所有列的列表。

伪代码：

1
2
3
4
5
6
7
8
9
10

foreach $column ($cols) {
query("SELECT count(*) FROM table WHERE $column IS NOT NULL")
if($result is zero) {
# $column contains only null values"
push @onlyNullColumns, $column;
} else {
# $column contains non-null values
}
}
return @onlyNullColumns;

我知道这似乎有点违反直觉，但是SQL没有提供本机选择列(仅行)的方法。

您可能需要澄清一下。您真正想完成什么？如果您真的想找出仅包含空值的列名，那么您将不得不遍历scheama并基于此进行动态查询。

我不知道您使用的是哪个DBMS，所以我将在此处放置一些伪代码。

1
2
3
4
5

for each col
begin
@cmd = 'if not exists (select * from tablename where ' + col + ' is not null begin print ' + col + ' end'
exec(@cmd)
end