Find Null percentage in each Column of Each Table in a DB


  

 Create  procedure [dbo].[usp_CountNullFromEachTable]
 
 @SCHEMA_TABLE_NAME NVARCHAR(100)  
 as

 
SET NOCOUNT ON;
  DECLARE @sql NVARCHAR(4000),
 @col NVARCHAR(50)
 
DECLARE c_cursor CURSOR FOR
  SELECT column_Name
  FROM   [INFORMATION_SCHEMA].[COLUMNS]
  WHERE  TABLE_NAME = parsename(@SCHEMA_TABLE_NAME,1)
         AND TABLE_SCHEMA = parsename(@SCHEMA_TABLE_NAME,2)
         AND IS_Nullable = 'YES'
OPEN c_cursor;
FETCH NEXT FROM c_cursor INTO @col;
WHILE ( @@FETCH_STATUS = 0 )
  BEGIN
      SET @sql = N' INSERT INTO CountNullFromEachTable (ColName, [NULL%], SCHEMA_TABLE_NAME)
        SELECT TOP 1 ''' + @col
                 + ''' , (COUNT(*) over() *1.0- COUNT(' + Quotename(@col) 
                 + ') OVER()*1.0)/NULLIF(COUNT(*) Over() ,0) *100.0 as [NULL%], '''
				 +@SCHEMA_TABLE_NAME+''' FROM '
                 + @SCHEMA_TABLE_NAME
 
      EXEC (@sql);
      FETCH NEXT FROM c_cursor INTO @col;
  END
CLOSE c_cursor;
DEALLOCATE c_cursor;
 
 


  IF (EXISTS (SELECT 1 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'dbo' 
                 AND  TABLE_NAME = 'CountNullFromEachTable'))
BEGIN
   drop table dbo.CountNullFromEachTable
END

 CREATE TABLE dbo.CountNullFromEachTable
  (
     id      INT IDENTITY(1, 1),
     ColName VARCHAR(50),
     [NULL%] DECIMAL(8, 2)
	 ,SCHEMA_TABLE_NAME NVARCHAR(50)
  )

  
SET ANSI_WARNINGS OFF;
exec sp_msforeachtable "exec dbo.[usp_CountNullFromEachTable] '?' "
--or
--exec sp_msforeachtable 'exec dbo.[usp_CountNullFromEachTable] ''?'' '


SELECT ColName,  [NULL%], SCHEMA_TABLE_NAME 
FROM dbo.CountNullFromEachTable




  

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ac43bf15-9bbb-4051-af87-f8638204fc2e/how-can-i-find-percentage-nulls-in-each-field-in-each-table-in-a-sql-server-db?forum=transactsql

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 )

Google photo

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