SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE TestNulls (
    ID            INT           NOT NULL,
    Name          VARCHAR(50)   NULL,
    Email         VARCHAR(100)  NULL,
    Age           INT           NULL,
    LastLogin     DATETIME      NULL
);

INSERT INTO TestNulls (ID, Name, Email, Age, LastLogin) VALUES
(1,  'Alice',   'alice@example.com',    25,  '2024-01-01'),
(2,  'Bob',     NULL,                   32,  NULL),
(3,  NULL,      'charlie@example.com',  NULL, NULL),
(4,  'Diana',   NULL,                   40,  '2024-03-10'),
(5,  NULL,      NULL,                   NULL, NULL),
(6,  'Eva',     'eva@example.com',      19,  '2024-02-11'),
(7,  'Frank',   NULL,                   NULL, '2024-02-22'),
(8,  NULL,      'henry@example.com',    55,  NULL),
(9,  'Irene',   NULL,                   28,  '2024-04-01'),
(10, NULL,      NULL,                   33,  '2024-05-05'),
(11, 'John',    'john@example.com',     NULL, NULL),
(12, NULL,      'kate@example.com',     44,  '2024-06-06');
12 rows affected

CREATE OR ALTER PROCEDURE GetNullPercentages
    @SchemaName sysname = 'dbo',
    @TableName  sysname
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql nvarchar(max);

    SELECT @sql = STRING_AGG(
        'SELECT ''' + c.COLUMN_NAME + ''' AS column_name,
                1-COUNT(['+c.COLUMN_NAME+'])/CAST(COUNT(*) AS FLOAT) AS null_percentage
         FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName),
        ' UNION ALL '
    )
    FROM INFORMATION_SCHEMA.COLUMNS c
    WHERE c.TABLE_SCHEMA = @SchemaName
      AND c.TABLE_NAME   = @TableName;

    IF @sql IS NULL
    BEGIN
        THROW 50000, 'Table not found.', 1;
    END;

    EXEC (@sql);
END;
EXEC GetNullPercentages 'dbo', 'TestNulls';


column_name
null_percentage


ID
0

Name
0.416666666666667

Email
0.5

Age
0.333333333333333

LastLogin
0.416666666666667


Warning: Null value is eliminated by an aggregate or other SET operation.


select 1-cast(count(Name) as float)/count(*)
from TestNulls


(No column name)


0.416666666666667


Warning: Null value is eliminated by an aggregate or other SET operation.
column_name	null_percentage
ID	0
Name	0.416666666666667
Email	0.5
Age	0.333333333333333
LastLogin	0.416666666666667