How to Find Percentage of Empty or Blank Values in every Column of all the Tables in SQL Server Database -SQL Server ?

To find the percentage of empty or blank values in every column of all the tables in a SQL Server database, you can modify the script to check for empty or blank values (e.g., strings with only whitespace). Here’s an example script:

DECLARE @TableName NVARCHAR(255)
DECLARE @ColumnName NVARCHAR(255)
DECLARE @TotalRows INT
DECLARE @EmptyCount INT
DECLARE @PercentageEmpty FLOAT

DECLARE tables_cursor CURSOR FOR
SELECT table_name = t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_TYPE = 'BASE TABLE'

OPEN tables_cursor
FETCH NEXT FROM tables_cursor INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE columns_cursor CURSOR FOR
    SELECT column_name = c.COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS c
    WHERE c.TABLE_NAME = @TableName

    OPEN columns_cursor
    FETCH NEXT FROM columns_cursor INTO @ColumnName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Calculate percentage of empty values for each column in the current table
        SET @EmptyCount = 0
        SET @TotalRows = 0

        -- Dynamic SQL to get empty count and total row count for the column
        DECLARE @DynamicSQL NVARCHAR(MAX)
        SET @DynamicSQL = 
            'SELECT @EmptyCount = COUNT(*) FROM ' + QUOTENAME(@TableName) + 
            ' WHERE NULLIF(LTRIM(RTRIM(' + QUOTENAME(@ColumnName) + ')), '''') IS NULL;' +
            'SELECT @TotalRows = COUNT(*) FROM ' + QUOTENAME(@TableName) + ';'

        EXEC sp_executesql @DynamicSQL, N'@EmptyCount INT OUTPUT, @TotalRows INT OUTPUT', 
            @EmptyCount OUTPUT, @TotalRows OUTPUT

        -- Calculate percentage of empty values
        IF @TotalRows > 0
        BEGIN
            SET @PercentageEmpty = (@EmptyCount * 100.0) / @TotalRows
        END
        ELSE
        BEGIN
            SET @PercentageEmpty = 0
        END

        -- Print results
        PRINT 'Table: ' + @TableName + ', Column: ' + @ColumnName +
              ', Percentage of Empty Values: ' + CONVERT(NVARCHAR(10), @PercentageEmpty) + '%'

        FETCH NEXT FROM columns_cursor INTO @ColumnName
    END

    CLOSE columns_cursor
    DEALLOCATE columns_cursor

    FETCH NEXT FROM tables_cursor INTO @TableName
END

CLOSE tables_cursor
DEALLOCATE tables_cursor

This script uses the LTRIM(RTRIM()) functions to remove leading and trailing spaces from each column value. The NULLIF function is then used to convert empty strings to NULL, and the percentage of empty values is calculated accordingly.

Please note that this script assumes that empty values are represented by either actual empty strings or strings consisting only of whitespace characters. If your definition of empty values is different, you may need to adjust the script accordingly. As always, be cautious about potential security risks and validate user inputs in dynamic SQL scripts.

--Let me explain the script step by step:

1.Declare Variables:

  
   DECLARE @TableName NVARCHAR(255)
   DECLARE @ColumnName NVARCHAR(255)
   DECLARE @TotalRows INT
   DECLARE @EmptyCount INT
   DECLARE @PercentageEmpty FLOAT
 

 --These variables are used to store the current table name, column name, total row count, empty count, and percentage of empty values.

2.Declare Cursors for Tables and Columns:

   DECLARE tables_cursor CURSOR FOR
   SELECT table_name = t.TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES t
   WHERE t.TABLE_TYPE = 'BASE TABLE'

   OPEN tables_cursor
   FETCH NEXT FROM tables_cursor INTO @TableName
 
--A cursor is used to iterate through the tables in the database. It selects the names of all base tables from the `INFORMATION_SCHEMA.TABLES` view.

3.Loop Through Tables:

   WHILE @@FETCH_STATUS = 0
   BEGIN


--The script enters a loop that iterates through each table.

4.Declare Cursor for Columns:


       DECLARE columns_cursor CURSOR FOR
       SELECT column_name = c.COLUMN_NAME
       FROM INFORMATION_SCHEMA.COLUMNS c
       WHERE c.TABLE_NAME = @TableName

       OPEN columns_cursor
       FETCH NEXT FROM columns_cursor INTO @ColumnName

 --Within the loop for tables, another cursor is used to iterate through the columns of the current table. It selects column names from the `INFORMATION_SCHEMA.COLUMNS` view based on the current table name.

5.Loop Through Columns:

  
       WHILE @@FETCH_STATUS = 0
       BEGIN
  

 --Inside the loop for columns, the script calculates the percentage of empty values for the current column in the current table.

6.Dynamic SQL to Get Empty Count and Total Row Count:

           SET @EmptyCount = 0
           SET @TotalRows = 0

           -- Dynamic SQL to get empty count and total row count for the column
           DECLARE @DynamicSQL NVARCHAR(MAX)
           SET @DynamicSQL = 
               'SELECT @EmptyCount = COUNT(*) FROM ' + QUOTENAME(@TableName) + 
               ' WHERE NULLIF(LTRIM(RTRIM(' + QUOTENAME(@ColumnName) + ')), '''') IS NULL;' +
               'SELECT @TotalRows = COUNT(*) FROM ' + QUOTENAME(@TableName) + ';'

           EXEC sp_executesql @DynamicSQL, N'@EmptyCount INT OUTPUT, @TotalRows INT OUTPUT', 
               @EmptyCount OUTPUT, @TotalRows OUTPUT


   The script uses dynamic SQL to execute a query for the current column. It uses `LTRIM(RTRIM())` to remove leading and trailing spaces and `NULLIF` to convert empty strings to `NULL`. The total row count and empty count are retrieved using `COUNT`.

7.Calculate Percentage of Empty Values:

           -- Calculate percentage of empty values
           IF @TotalRows > 0
           BEGIN
               SET @PercentageEmpty = (@EmptyCount * 100.0) / @TotalRows
           END
           ELSE
           BEGIN
               SET @PercentageEmpty = 0
           END
 --The script calculates the percentage of empty values by dividing the empty count by the total row count and multiplying by 100.

8.Print Results:

           -- Print results
           PRINT 'Table: ' + @TableName + ', Column: ' + @ColumnName +
                 ', Percentage of Empty Values: ' + CONVERT(NVARCHAR(10), @PercentageEmpty) + '%'

--Finally, the script prints the results, including the table name, column name, and the calculated percentage of empty values.

9.Fetch Next Column:

           FETCH NEXT FROM columns_cursor INTO @ColumnName
       END

       CLOSE columns_cursor
       DEALLOCATE columns_cursor

--After processing all columns of a table, the inner cursor is closed and deallocated.

10.Fetch Next Table:

       FETCH NEXT FROM tables_cursor INTO @TableName
   END

 --After processing all tables, the outer cursor is closed and deallocated.

11.Close Cursors:

   CLOSE tables_cursor
   DEALLOCATE tables_cursor

--The script closes and deallocates the cursors.
--This script dynamically calculates and prints the percentage of empty values for each column in every table of the SQL Server database.