Check Rows’ NULL Percentage For a Given Table –T-SQL


–USE YourDB
–GO
DECLARE @colsCast NVARCHAR(4000)
, @cols NVARCHAR(4000)
, @sql1 NVARCHAR(4000)
, @sql2 NVARCHAR(4000)
, @Schema_Name NVARCHAR(4000) =’dbo’
, @TABLE_NAME NVARCHAR(4000) =’test1′
,@num NVARCHAR(4000)

SELECT @colsCast = COALESCE(@colsCast + ‘, ‘, ”) + ‘CAST(‘+ Quotename(column_Name)+’ AS NVARCHAR(4000)) AS ‘+ Quotename(column_Name)
, @cols = COALESCE(@cols + ‘, ‘, ”) + Quotename(column_Name) , @num=count(*) over()
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLE_NAME
print @cols

–You can modify the WHERE clause to filter your original table

SET @sql1=’IF OBJECT_ID(N”dbo.tmp”,N”U”) IS NOT NULL
Begin
DROP Table dbo.tmp
END

Begin
SELECT * INTO tmp
FROM ‘+ Quotename(@TABLE_NAME) + ‘ WHERE 1=1
END’

EXEC sp_executesql @sql1

SET @sql2=’WITH mycte AS
(
SELECT rn, col, val FROM (SELECT ‘+ @colsCast+ ‘, row_number() OVER(Order by ‘ + @cols+’ ) rn FROM tmp) src
UNPIVOT (val For col IN ( ‘+ @cols+ ‘)) unpvt )
, mycte2 AS ( SELECT rn, col, val, COUNT(val) OVER(Partition by rn) cnt , ‘+@num+’ as cntAll FROM mycte )
, mycte3 AS (SELECT rn, col, val, row_number() OVER(Order by cnt DESC ) rn2 , CAST(cnt*1.0/ cntALL*100 as decimal(6,2)) as NullPercentForRow FROM mycte2)

SELECT ‘+ @cols+ ‘, NullPercentForRow from (select rn, col, val, NullPercentForRow FROM mycte3
–WHERE rn=(select rn from mycte3 WHERE rn2=1 )
) src
PIVOT (max(val) FOR col IN ( ‘+ @cols+ ‘)) pvt ‘

print @sql2

EXEC sp_executesql @sql2

Advertisements


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