I was asked if I could work out the number of null fields in a database table.
Below is what I came up with, probably a lot of ways to do this, however this seems to work quite well. Run time was 1 min 12 seconds, on a new 4 Gig Dell Vostro for a table with 118 columns, 1.2 million rows and 99 million null cells (which seems to be a lot of empty cells to me…) and no indexing.
Basic concept is to pull the column names from the INFORMATION_SCHEMA.COLUMNS view, loop through each one and execute a SELECT COUNT where COLUMN IS NULL, add count to a running total variable.
To alter for use you would need to change the database_name and table_name in the CURSOR declaration and the table_name in the SET @SQL string.
This was a SQL Server 2008 database but should work with other versions.
DECLARE @SQL NVARCHAR(MAX) -- VARIABLE FOR SQL STRING DECLARE @COL_NAME NVARCHAR(500) -- VARIBLE TO HOLD EACH COLUMN NAME DECLARE @NULL_COUNT FLOAT -- VARIABLE FOR COUNT OF NULLS IN EACH COLUMN DECLARE @TOTAL_NULLS FLOAT = 0 -- RUNNING TOTAL FOR NULL FIELDS DECLARE @PARAM_DEF NVARCHAR(500) -- VARIABLE FOR THE PARAMETER DEFINITION --CURSOR TO HOLD FIELD NAMES FOR THE SLECTED TABLE DECLARE FIELD_CURSOR CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = 'COFELY_DATA' AND TABLE_NAME = 'SAC_LOG' OPEN FIELD_CURSOR --COPY FIRST FIELD NAME INTO @COL_NAME FETCH NEXT FROM FIELD_CURSOR INTO @COL_NAME --LOOP WHILE CURSOR STILL RETURNS A VALUE WHILE @@FETCH_STATUS = 0 BEGIN --BUILD SQL TO SELECT COUNT OF NULLS FROM COLUMN INTO @NULL_COUNT_OUT SET @SQL = ' SELECT @NULL_COUNT_OUT = COUNT(*) FROM bags.SAC_LOG WHERE [' + @COL_NAME + '] IS NULL ' --SET THE @NULL_COUNT_OUT PARAMETER SET @PARAM_DEF = '@NULL_COUNT_OUT FLOAT OUTPUT' --EXECUTE THE sp_executesql STORED PROC PASSING THE @NULL_COUNT_OUT RETURN VALUE INTO THE @NULL_COUNT VARIABLE EXEC sp_executesql @SQL, @PARAM_DEF, @NULL_COUNT_OUT = @NULL_COUNT OUTPUT --UPDATE THE RUNNING TOTAL SET @TOTAL_NULLS = @TOTAL_NULLS + @NULL_COUNT --GET NEXT COLUMN NAME FROM THE CURSOR FETCH NEXT FROM FIELD_CURSOR INTO @COL_NAME END --CLOSE AND DEALLOCATE THE CURSOR CLOSE FIELD_CURSOR DEALLOCATE FIELD_CURSOR --SELECT TOTAL NULL COUNT SELECT @TOTAL_NULLS