How to Find Percentage of Null Values in every Column of all the Tables in SQL Server Database - SQL Server Tutorial / TSQL Tutorial

Scenario:  Download Script

You are working as SQL Server / TSQL Developer in Auto Insurance Company. You are working on data validation and data analysis for your project. You got this requirement in which you need to find the Percentage of Null values in every column for all the tables in SQL Server Database.

This can be really helpful in analysis so you would know how much data you are really getting or you are getting no values( Null) and plan the storage. Also depending upon the analysis you might find some records those should not be Null but you have receive Null for those Columns, so data corrections can be next step.

Solution:

We are going to use Cursor to find percentage and Null Row Count in each of the column in all the tables in SQL Server Database. This process can take long time to calculate as it will be running for each column in database if Column allows Nulls. If Column is Not Nullable , we don't need to check that columns as nobody can insert Null values in those columns.


I will suggest to run the script on small database and take the backup of production database and restore to UAT and run query on database in UAT.  In case your database is small, You might be fine. Again you don't want to hurt performance of running processes,You might want to schedule to run after hours.

USE YourDBName
GO

DECLARE @DatabaseName VARCHAR(100)
DECLARE @SchemaName VARCHAR(100)
DECLARE @TableName VARCHAR(100)
DECLARE @ColumnName VARCHAR(100)
DECLARE @FullyQualifiedTableName VARCHAR(500)

--Create Temp Table to Save Results
IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results

CREATE TABLE #Results (
    DatabaseName VARCHAR(100)
    ,SchemaName VARCHAR(100)
    ,TableName VARCHAR(100)
    ,ColumnName VARCHAR(100)
    ,TotalTableRowCount INT
    ,NullRecordCount INT
    )

DECLARE Cur CURSOR
FOR
SELECT C.Table_CataLog
    ,C.Table_Schema
    ,C.Table_Name
    ,C.Column_Name
    ,'[' + C.Table_CataLog + ']' + '.[' + C.Table_Schema + '].' 
    + '[' + C.Table_Name + ']' AS FullQualifiedTableName
FROM information_schema.Columns C
INNER JOIN information_Schema.Tables T ON C.Table_Name = T.Table_Name
    AND T.Table_Type = 'BASE TABLE'
    and C.IS_Nullable='YES'


OPEN Cur

FETCH NEXT
FROM Cur
INTO @DatabaseName
    ,@SchemaName
    ,@TableName
    ,@ColumnName
    ,@FullyQualifiedTableName

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL VARCHAR(MAX) = NULL

    SET @SQL = ' Select ''' + @DatabaseName + ''' AS DatabaseName, ''' 
    + @SchemaName + ''' AS TableName,
      ''' + @TableName + ''' AS SchemaName,
      ''' + @ColumnName + ''' AS ColumnName,
      (Select count(*) from ' + @FullyQualifiedTableName + ' with (nolock)) 
      AS TotalTableRowCount,
      count(*) as NullRecordCount from ' + @FullyQualifiedTableName 
      + 'with (nolock) Where ' + @ColumnName + ' IS NULL' 

    --Print @SQL
    INSERT INTO #Results
    EXEC (@SQL)

    FETCH NEXT
    FROM Cur
    INTO @DatabaseName
        ,@SchemaName
        ,@TableName
        ,@ColumnName
        ,@FullyQualifiedTableName
END

CLOSE Cur

DEALLOCATE Cur


SELECT *,
Cast((NullRecordCount/Cast(TotalTableRowCount as Numeric(13,1)))*100 AS Numeric(4,1))

 AS NullPercentColumnValues
FROM #Results
where NullRecordCount <>0
--drop table #Results

Here are my results when I ran above query on one of my database, it will return you DatabaseName,SchemaName,TableName,ColumnName,TotalTableRowCount,NullRecordCount and Null Percentage Column Values as shown below.

How to get Null Value count for all the column in SQL Server Database - SQL Server Tutorial

Check out our other posts on Data Analysis / Data Validation / Data Cleansing

• How to Search in all Columns for all tables in a database for Date Value in SQL Server
• How to Find Percentage of Empty or Blank Values in every Column of all the Tables in SQL Server Database
• How to Get Row Count Of All The Tables In SQL Server Database
• How to search for a String in all Columns in all tables in SQL Server Database
• How to find and Replace Text in all the columns in all the tables in SQL server Database
• How to find values with Leading or Trailing Spaces in SQL Server Table 
• How to Trim Leading and Trailing spaces for all the values in all the Columns in all the Tables in a SQL Server Database
• How to Find Duplicate Records in All the Tables in SQL Server Database
• How to Find & Delete Duplicate Records on All Tables in SQL Server Database
• How to Generate Select all Columns with or Without Top X Rows From All Tables Query Dynamically in SQL Server
• Concatenate SQL Server Columns for each Table Query Dynamically in SQL Server 
• How to List all Missing Identity Values for all Tables in SQL Server Database
• How to Find Max and Min Values for all the columns for all the tables in SQL Server Database