CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
Indoordinosaur
Indoordinosaur

Reputation: 139

Return all rows where at least one value in any of the columns is 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.

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

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.

Upvotes: 1

Views: 2210

Answers (3)

Grignar
Grignar

Reputation: 11

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.

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.

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

Upvotes: 1

Dan Field
Dan Field

Reputation: 21641

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

Here's a slightly different approach, using 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 clashes 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....

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.

Upvotes: 0

Dr. Wily's Apprentice
Dr. Wily's Apprentice

Reputation: 10280

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

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

Upvotes: 4

Related Questions