SQL script to list columns with NULL values for all the records

Recently, my client asked me to give him the list of columns which have NULL value for all the records of that Table. This has to be done on the CRM database.

There might be better ways to fetch this information but this is what I did

• Find out total number of rows present in concerned table
• List the columns without NOT NULL constraint
• Loop over this column list using cursor by substituting it in Count(column name) in a dynamic query

Given below is the script that I came-up with .. Please make sure to replace Contactbase (table name) with the table name that you want to find information for. And use appropriate database context.

DECLARE @Query NVARCHAR(4000),
        @Count INT

DECLARE @TAB TABLE
(
Query VARCHAR(4000)
)
DECLARE @Result TABLE
(
TableName VARCHAR(100),
Columnname VARCHAR(100),
NullrecordCount INT
)
SELECT @Count = COUNT(1) FROM ContactBase

INSERT INTO @TAB
SELECT
      'SELECT ''' + TABLE_NAME + ''' AS TableName,'''+ COLUMN_NAME +''' AS ColumnName, COUNT(1) CNT FROM ['
      + TABLE_NAME + '] WHERE [' + COLUMN_NAME + '] IS NULL ' 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE IS_NULLABLE = 'YES' AND TABLE_NAME = 'ContactBase'

DECLARE FindNull CURSOR FOR
SELECT * FROM @TAB

OPEN FindNull

FETCH NEXT FROM FindNull INTO @Query
WHILE @@FETCH_STATUS = 0
BEGIN

      INSERT INTO @Result
      EXEC sp_EXECUTESQL @Query

      FETCH NEXT FROM FindNull INTO @Query
END

CLOSE FindNull

DEALLOCATE FindNull

SELECT * FROM @Result
WHERE NullrecordCount = @Count