Check Columns’ NULL Percentage For a Given Table –T-SQL(Three Solutions)


Solution 1: Using dynamic query with COUNT(col) and COUNT(*).

DECLARE @DBName VARCHAR(50)=’AdventureWorks2012′,

@TABLE_NAME VARCHAR(50) = ‘Product’,

@TABLE_SCHEMA VARCHAR(50) = ‘Production’

DECLARE @sql NVARCHAR(4000)

SELECT @sql = COALESCE(@sql + ‘, ‘, ‘SELECT ‘)

+ ‘ CAST((count(*)- CAST(count( ‘

+ Quotename(column_Name)

+ ‘) as decimal(8,2)))/Count(*)*100.00 as decimal(8,2)) as [‘

+ column_Name + ‘– NULL Value %] ‘

— CASE WHEN IS_Nullable=’YES’ THEN ‘– NULL Value %] ‘ ELSE ‘–Not Nullable column]’ END

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @TABLE_NAME

AND TABLE_SCHEMA = @TABLE_SCHEMA

AND IS_Nullable=’YES’

SET @sql =@sql + ‘ FROM ‘ + Quotename(@DBName) + ‘.’

+ Quotename(@TABLE_SCHEMA) + ‘.’

+ Quotename(@TABLE_NAME)

EXECUTE master..Sp_executesql @sql

–PRINT @sql

–Based on that Null value is eliminated by an aggregate operation. I choose COUNT().

–Soution 2: Using cursor.

USE [AdventureWorks2012]

go

SET NOCOUNT ON;

DECLARE @TABLE_NAME NVARCHAR(50) = ‘Product’,

@TABLE_SCHEMA NVARCHAR(50) = ‘Production’,

@sql NVARCHAR(4000),

@col NVARCHAR(50)

CREATE TABLE #t

(

id INT IDENTITY(1, 1),

ColName VARCHAR(50),

[NULL%] DECIMAL(8, 2)

)

DECLARE c_cursor CURSOR FOR

SELECT column_Name

FROM [INFORMATION_SCHEMA].[COLUMNS]

WHERE TABLE_NAME = @TABLE_NAME

AND TABLE_SCHEMA = @TABLE_SCHEMA

AND IS_Nullable = ‘YES’

OPEN c_cursor;

FETCH NEXT FROM c_cursor INTO @col;

WHILE ( @@FETCH_STATUS = 0 )

BEGIN

SET @sql = N’ insert into #t (ColName, [NULL%])

SELECT TOP 1 ”’ + @col

+ ”’ , (COUNT(*) over() *1.0- COUNT(‘ + @col

+ ‘) OVER()*1.0)/COUNT(*) over() *100.0 as [NULL%] FROM ‘

+ Quotename(@TABLE_SCHEMA) + ‘.’

+ Quotename( @TABLE_NAME)

— print @sql

EXEC (@sql);

FETCH NEXT FROM c_cursor INTO @col;

END

CLOSE c_cursor;

DEALLOCATE c_cursor;

SELECT ColName, [NULL%] FROM #t

DROP TABLE #t

 

3.Solution three:

DECLARE @sql nvarchar(4000)

DECLARE @sql2 nvarchar(4000)

DECLARE @DBName nvarchar(100)=’AdventureWorks2012′

DECLARE @Table_Name nvarchar(100)=’Product’

DECLARE @TABLE_SCHEMA nvarchar(100)=’Production’

SET @sql = ‘SELECT * into ##Cols FROM OPENQUERY([MC046537\IFBF2012],”exec(””use ‘+@DBName+ ‘ exec sp_columns ‘+ @Table_Name +’,’ + @TABLE_SCHEMA+ ””’)”)’

–print(@sql)

EXECUTE sp_executesql @sql

SELECT @sql2 = COALESCE(@sql2 + ‘, ‘, ‘SELECT ‘)

+ ‘ CAST((count(*)- CAST(count( ‘

+ column_Name

+ ‘) as decimal(8,2)))/Count(*)*100.00 as decimal(8,2)) as [‘ + column_Name + ‘– NULL Value %] ‘

 

FROM ##Cols

WHERE Table_QUALIFIER = @DBName AND TABLE_NAME = @TABLE_NAME AND TABLE_OWNER= @TABLE_SCHEMA AND IS_Nullable=’YES’

SET @sql2 = @sql2 +’ FROM ‘+@DBName+’.’+@TABLE_SCHEMA+’.’+ @Table_Name

–print (@sql2)

–exec(@sql2)

EXECUTE sp_executesql @sql2

drop Table ##Cols

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