返回任何列中至少有一个值为null的所有行

I have just completed the process of loading new tables with data. I'm currently trying to validate the data. The way I have designed my database there really shouldn't be any values anywhere that are NULL so i'm trying to find all rows with any NULL value.

我刚刚完成了使用数据加载新表的过程。我目前正在尝试验证数据。我设计我的数据库的方式实际上不应该是任何值为NULL的值，所以我试图找到所有具有任何NULL值的行。

Is there a quick and easy way to do this instead of writing a lengthy WHERE clause with OR statements checking each column?

是否有一种快速简便的方法来执行此操作，而不是使用OR语句检查每列来编写冗长的WHERE子句？

UPDATE: A little more detail... NULL values are valid initially as sometimes the data is missing. It just helps me find out what data I need to hunt down elsewhere. Some of my tables have over 50 columns so writing out the whole WHERE clause is not convenient.

更新：更多详细信息... NULL值最初有效，因为有时数据丢失。它只是帮助我找出我需要在其他地方寻找的数据。我的一些表有50多列，所以写出整个WHERE子句并不方便。

3 个解决方案

#1

Write a query against Information_Schema.Columns (documentation) that outputs the SQL for your very long where clause.

对Information_Schema.Columns（文档）编写一个查询，为您的long where子句输出SQL。

Here's something to get you started:

这是让你入门的东西：

select 'OR ([' + TABLE_NAME + '].[' + TABLE_SCHEMA + '].[' + COLUMN_NAME + '] IS NULL)'
from mydatabase.Information_Schema.Columns
order by TABLE_NAME, ORDINAL_POSITION

#2

The short version answer, use SET CONCAT_NULL_YIELDS_NULL ON and bung the whole thing together as a string and check that for NULL (once). That way any null will propagate through to make the whole row comparison null.

短版本答案，使用SET CONCAT_NULL_YIELDS_NULL ON并将整个事物作为字符串一起打包并检查是否为NULL（一次）。这样，任何null都将传播以使整行比较为null。

Here's the silly sample code to demo the principal, up to you if you want to wrap that in an auto-generating schema script (to only check Nullable columns and do all the appropriate conversions). Efficient it ain't, but almost any way you cut it you will need to do a table scan anyway.

这里是演示主体的愚蠢的示例代码，如果您想将其包装在自动生成的模式脚本中（仅检查Nullable列并执行所有适当的转换）。效率不高，但几乎任何削减方式，无论如何都需要进行表格扫描。

CREATE TABLE dbo.Example
(
PK INT PRIMARY KEY CLUSTERED IDENTITY(1,1),
A nchar(10) NULL,
B int NULL,
C nvarchar(50) NULL
)  ON [PRIMARY]
GO

INSERT dbo.Example(A, B, C)
VALUES('Your Name', 1, 'Not blank'),
('My Name', 3, NULL),
('His Name', NULL, 'Not blank'),
(NULL, 5, 'It''s blank');

SET CONCAT_NULL_YIELDS_NULL ON 
SELECT E.PK 
FROM dbo.Example E
WHERE (E.A + CONVERT(VARCHAR(32), E.B) + E.C) IS NULL
SET CONCAT_NULL_YIELDS_NULL OFF

#3

As mentioned in a comment, if you really expect columns to not be null, then put NOT NULL constraints on them. That said...

正如评论中所提到的，如果你真的希望列不为null，那么就对它们加上NOT NULL约束。那说......

Here's a slightly different approach, using INFORMATION_SCHEMA:

这是一种略有不同的方法，使用INFORMATION_SCHEMA：

DECLARE @sql NVARCHAR(max) = '';

SELECT @sql = @sql + 'UNION ALL SELECT ''' + cnull.TABLE_NAME + ''' as TableName, ''' 
                                           + cnull.COLUMN_NAME + ''' as NullColumnName, '''
                                           + pk.COLUMN_NAME + ''' as PkColumnName,' +
                                           + 'CAST(' + pk.COLUMN_NAME + ' AS VARCHAR(500)) as PkValue '
                                           + ' FROM ' + cnull.TABLE_SCHEMA + '.' + cnull.TABLE_NAME 
                                           + ' WHERE ' +cnull.COLUMN_NAME + ' IS NULL '
FROM INFORMATION_SCHEMA.COLUMNS cnull
INNER JOIN (SELECT Col.Column_Name, col.TABLE_NAME, col.TABLE_SCHEMA
                from INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab
                INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col 
                ON  Col.Constraint_Name = Tab.Constraint_Name AND Col.Table_Name = Tab.Table_Name
                WHERE CONSTRAINT_TYPE = 'PRIMARY KEY') pk
ON pk.TABLE_NAME = cnull.TABLE_NAME AND cnull.TABLE_SCHEMA = pk.TABLE_SCHEMA
WHERE cnull.IS_NULLABLE = 'YES'

set @sql = SUBSTRING(@sql, 11, LEN(@sql)) -- remove the initial 'UNION ALL '

exec(@sql)

Rather a huge where clause, this will tell you the primary key on the table where any field in that table is null. Note that I'm CASTing all primary key values to avoid operand *es if you have some that are int/varchar/uniqueidentifier etc. If you have a PK that doesn't fit into a VARCHAR(500) you probably have other problems....

相反，它是一个巨大的where子句，它将告诉您表中的主键，该表中的任何字段都为null。请注意，如果您有一些int / varchar / uniqueidentifier等，我会对所有主键值进行CASTing以避免操作数冲突。如果您的PK不适合VARCHAR（500），您可能还有其他问题。 ..

This would probably need some tweaking if you have any tables with composite primary keys - as it is, I'm pretty sure it would just output separate rows for each member of the key instead of concatenating them, and wouldn't necessarily group them together the way you'd want.

如果您有任何具有复合主键的表，这可能需要一些调整 - 因为它是，我很确定它只会为键的每个成员输出单独的行而不是连接它们，并且不一定将它们组合在一起你想要的方式。

One other thought would be to just SELECT * from ever table and save the output to a format (Excel, plain text csv) you can easily search for the string NULL.

另一个想法是从ever表中只选择SELECT *并将输出保存为格式（Excel，纯文本csv），您可以轻松搜索字符串NULL。

#1