Checking a wide table for nulls [duplicate]

Question

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.

Answer 1

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]
  

Answer 2

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);