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

Basically To find the percentage of null values in every column of all the tables in a SQL Server database, you can use a dynamic SQL query along with the INFORMATION_SCHEMA.COLUMNS view to retrieve information about columns. Here's an example script to get you started:

DECLARE @TableName NVARCHAR(255)
DECLARE @ColumnName NVARCHAR(255)
DECLARE @TotalRows INT
DECLARE @NullCount INT
DECLARE @PercentageNull 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 null values for each column in the current table
        SET @NullCount = 0
        SET @TotalRows = 0

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

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

        -- Calculate percentage of null values
        IF @TotalRows > 0
        BEGIN
            SET @PercentageNull = (@NullCount * 100.0) / @TotalRows
        END
        ELSE
        BEGIN
            SET @PercentageNull = 0
        END

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

        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 two cursors to iterate through tables and columns, and for each column, it dynamically calculates the percentage of null values and prints the result.

Note: As with any script involving dynamic SQL, be cautious about potential security risks. Validate user inputs and ensure the script is used in a secure environment.