Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Possible Duplicate:
Test if any fields are NULL

I am new here. I have a table with 70-odd columns. Is there a way I can check the number of rows with at least one null value in any column other than writing something like ...WHERE Col1 IS NULL OR Col2 IS NULL OR...? Thanks in advance.

EDIT: The table is on a MS SQL Server 2008 instance. The columns in the table are of different datatypes.

share|improve this question

marked as duplicate by Martin Smith, Mark Storey-Smith, JNK Jul 5 '12 at 18:20

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2 Answers 2

Dynamic SQL:

DECLARE @SQL nvarchar(max);

SELECT
   @SQL = IsNull(@SQL + ' OR ', '') + QuoteName(Column_Name) + ' IS NULL'
FROM
   INFORMATION_SCHEMA.COLUMNS
WHERE
   Table_Name = 'YourTable'
   AND Table_Schema = 'YourSchema';

EXEC ('SELECT Count(*) FROM YourSchema.YourTable WHERE ' + @SQL);

Another way:

SET ANSI_NULLS OFF; -- deprecated in SQL 2005 - 2012, will eventually be removed

SELECT Count(*) FROM YourSchema.YourTable WHERE NULL IN (Col1, Col2, Col3, Col4 ... Col80);

And yet another way, assuming your column names don't have space character in them:

  • In SSMS, navigate to the table in the object browser and drop open the column list. Drag the "Columns" folder to a query window. Use search and replace to change all the spaces in the selection to IS NULL OR. Prepend the following code and run it!

    SELECT Count(*) FROM YourSchema.YourTable WHERE [put modified column list here]
    
share|improve this answer
    
What is the alternative to ...WHERE NULL IN... if we don't want to SET ANSI_NULLS OFF;? –  Anonymous Maximus Jul 5 '12 at 15:04
    
There is no alternative. You could unpivot if it's THAT important to you. –  ErikE Jul 5 '12 at 16:57

I'm not aware of any syntactical shortcut that lets you do this.

Here's an approach that uses dynamic SQL and the system catalog view sys.columns:

DECLARE @sql nvarchar(MAX);

SELECT
    @sql = COALESCE(@sql + N' OR (', N'SELECT COUNT(*) FROM [dbo].[mytable] WHERE (') +
        QUOTENAME(c.name) + N' IS NULL)'
    FROM sys.columns c
    WHERE
        (c.object_id = OBJECT_ID(N'[dbo].[mytable]')) AND
        (c.is_nullable = 1) --AND
        --(c.name NOT IN (/* Excluded column names */));

EXEC(@sql);
share|improve this answer
    
Thank you! I am still trying to wrap my head around this dynamic SQL example. Can you please explain the @sql part? I know the coalesce function and tried to separate the arguments out by comma location, but I am confused. What are the various N's for? –  Anonymous Maximus Jul 5 '12 at 14:23
1  
@Anon: The @sql = ... part gets run once per row returned by the query -- the first time, however, the variable is NULL, so we initialize it to SELECT COUNT(*) ... (this could be done in the DECLARE statement instead). Each subsequent time, it only concatenates the column name with the condition. The use of N in front of string literals signifies that the literal is a Unicode string -- SQL Server allows Unicode object names, so when doing dynamic SQL, it's a good idea to always use Unicode strings for maximum compatibility. –  Jon Seigel Jul 5 '12 at 14:42

Not the answer you're looking for? Browse other questions tagged or ask your own question.