1

I'm wondering if I can run an ad-hoc query just to see if a table contains any NULL values across any of its columns. The table has 100+ columns so doing this manually would be a huge pain.

davejal
  • 6,009
  • 10
  • 39
  • 82
intA
  • 2,513
  • 12
  • 41
  • 66
  • Do all (or at least most) 100+ columns *allow* NULL values? – Amit Nov 16 '15 at 14:01
  • Possible duplicate of [How to search for one value in any column of any table inside one MS-SQL database?](http://stackoverflow.com/questions/709120/how-to-search-for-one-value-in-any-column-of-any-table-inside-one-ms-sql-databas) – Matt Nov 16 '15 at 14:02

1 Answers1

4

You can try like this:

DECLARE @tb NVARCHAR(255) = N'dbo.[table]';

DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM ' + @tb
    + ' WHERE 1 = 0';

SELECT @sql += N' OR ' + QUOTENAME(name) + ' IS NULL'
    FROM sys.columns 
    WHERE [object_id] = OBJECT_ID(@tb);

EXEC sp_executesql @sql;

Source

Community
  • 1
  • 1
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331