Counting Null Fields in SQL Server Table

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
Advertisements
This entry was posted in Programming and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s