Find out columns with no data

Sometimes you may need to find out the fill rate of each column to know the accuracy of given data. One of the things to know is how many columns do not have any values at all.

Let us create the following data set

use tempdb 
create table test
	customer_id int, 
	customer_name varchar(100), 
	dob date, 
	address1 varchar(100),
	address2 varchar(100)

insert into test(customer_id,customer_name, dob)
select 10001,'Madhivanan','1990-01-01' union all
select 10002,'Murugan','1991-10-19' union all
select 10003,'Kandhan','1984-04-22'

Now see the result set

select * from test;

As you see, all the values of the columns address1 and address2 are NULL.

You can execute the following dynamic SQL to get the column names for which all values are NULL

declare @sql varchar(8000), @create varchar(1000),@insert varchar(100)

	@create='Create table #test (col_value sql_variant, col_name varchar(100)) ', 
	@insert=' insert into #test(col_value,col_name) '

select @sql=@sql+' '+@insert+
	' select min('+column_name+') as '+column_name+ ','''+column_name+
		''' from '+table_name 
	from information_schema.COLUMNS
	TABLE_NAME='test'  and table_catalog='tempdb'

set @sql=@create+' '+ @sql+ ' select * from #test where col_value is null'

After execution, the result is


It listed out two columns address1 and address1 which have all values as NULL. This code can be modified to handle empty calues or any other values easily


Leave a Reply

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

You are commenting using your 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 )

Connecting to %s