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.


2 Answers 2


Dynamic SQL:

DECLARE @SQL nvarchar(max);

   @SQL = IsNull(@SQL + ' OR ', '') + QuoteName(Column_Name) + ' IS NULL'
   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]
  • What is the alternative to ...WHERE NULL IN... if we don't want to SET ANSI_NULLS OFF;? Jul 5, 2012 at 15:04
  • There is no alternative. You could unpivot if it's THAT important to you.
    – ErikE
    Jul 5, 2012 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);

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

  • 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? Jul 5, 2012 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, 2012 at 14:42

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