How to Find Percentage of Empty or Blank 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 developer / TSQL Developer for Insurance company. You need to perform some analysis of data in one of the database. You are asked to provide Percentage of Empty or Blank Values in every Column of all the tables in Database. Don't mix Empty or Blank values with Null values. 

As blank or empty value ('') can be saved in string type columns with data type such as  char, nchar, varchar, nvarchar,text and ntext. We will be checking only in columns which has these data types.

Solution:

This information can be really helpful for analysis and we can see if we are loading or entering correct data. If corrections are required after analysis we can write update statements to fix the data.

For this requirement we will be using Cursor. As it is going to run on each column which has data type char, nchar, varchar,nvarchar,text or ntext. It is going to be expensive. If your database is small, you might be fine to run anytime. If you have a big database and it is used by many users or processes, you might want to run this code on weekends or after hours.

One other way can be, take the full back of your production database, restore to another server maybe UAT and run the script there.

USE YourDatabaseName

GO

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

--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)
    ,ColumnDataType VARCHAR(50)
    ,TotalTableRowCount INT
    ,EmptyRecordCount 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,
    C.Data_Type
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.Data_Type like '%CHAR%'
    or C.Data_Type like '%Text%')


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

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL VARCHAR(MAX) = NULL
    DECLARE @ColumnNameTRIM VARCHAR(50)
    IF @DataType like '%Text%'
    BEGIN 
    SET @ColumnNameTRIM='LTRIM(RTRIM(CAST('+@ColumnName + ' AS NVARCHAR(MAX))))'
    END 
    ELSE
    BEGIN 
    SET @ColumnNameTRIM='LTRIM(RTRIM('+@ColumnName + '))'
    END

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

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

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

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

AS EmptyValuesPercentPerColumn
from #Results
Where EmptyRecordCount<>0
--drop table #Results

Here are the result of above query for one of my database. 

 How to find Empty or Blank Row Count in each Column for a table in SQL Server Database

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 Null 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