This could probably be pared down a bit, or adjusted to accept a specific list of columns if you are interested only in a subset (For instance, you probably don't care about the distribution of a surrogate key, so you could exclude that. Or maybe you want to exclude float columns), but here is a snippet I use when I want to find the distinct values in a table, and the counts thereof.

-- Field-by-Field with loop
drop table if exists #DistVals
create table #DistVals
(
    TableName nvarchar(384),
    OrdinalPosition int,
    Field nvarchar(128),
    Type nvarchar(128),
    Value nvarchar(max),
    Ct int
)
go

declare 
    @TableName nvarchar(128), -- table here
    @SchemaName nvarchar(128) -- schema here
declare @sql nvarchar(max)
declare c cursor local fast_forward for
    select concat('insert into #DistVals (TableName, OrdinalPosition, Field, Type, Value, Ct) select TableName = ', quotename(concat(table_catalog, '.', table_schema, '.', table_name), ''''), ', OrdinalPosition = ', ordinal_position, ', Field = ', quotename(column_name, ''''), ', Type = ', quotename(data_type, ''''), ', Value = convert(nvarchar(max), ', quotename(column_name), '), Ct = count(1) from ', quotename(table_catalog), '.', quotename(table_schema), '.', quotename(table_name), ' group by ', quotename(column_name))
    from information_schema.columns
    where table_name = @TableName
        and table_schema = @SchemaName
        -- and column_name <> 'MySurrogateKey'
open c

fetch next from c into @sql

while @@fetch_status = 0
begin

    exec sp_executesql @sql
    
    fetch next from c into @sql

end

select
    TableName,
    OrdinalPosition,
    Field,
    Type,
    Value,
    Ct,
    TotalValueCt = sum(ct) over (partition by TableName, Field)
from #DistVals
order by TableName, OrdinalPosition, Ct desc

I find this approach the easiest to reason about, and since I'm typically running this in an ad-hoc fashion, I usually don't care that I have to re-hit the table n times for each column.

EDIT If you really want as many result sets as you have columns to analyze, you could replace the insert into #DistVals with a simple select. I don't know your exact use case, but to me, having a single dataset containing all columns is more useful.

That said, if you want to get everything in one go, You could also do that by doing a dynamic unpivot .

-- all at once with dynamic unpivot
declare 
    @TableName nvarchar(128),
    @SchemaName nvarchar(128)

declare 
    @Columns nvarchar(max),
    @ConvertedColumns nvarchar(max),
    @SQL nvarchar(max)

select 
    @Columns = string_agg(quotename(name), ','),
    @ConvertedColumns = string_agg(concat(quotename(name), ' = convert(nvarchar(max), ', quotename(name), ')'), ',')
from sys.columns
where object_id = object_id(@SchemaName + '.' + @TableName)

select @SQL = concat
('
    select
        Name,
        ValueCt = count(1),
        DistinctValueCt = count(distinct Value)
    from
    (
        select ', @ConvertedColumns, '
        from ', quotename(@SchemaName), '.', quotename(@TableName), '
    ) s
    unpivot(Value for Name in(', @Columns, ')) u
    group by name
')

exec sp_executesql @SQL

Here is another option leveraging the sys.columns to build the dynamic sql for this. It is a bit simpler than using a dynamic unpivot for something like this.

If you want the distinct counts in a single row per column it would be something like this.

declare @sql nvarchar(max) = 'select '
    , @TableName sysname = 'YourTableName'

select @sql = @sql + quotename(c.name) + ' = count(distinct ' + quotename(c.name) + '), '
from sys.columns c
where object_id = object_id(@TableName)

set @sql = left(@sql, len(@sql) - 1) + ' from ' + QUOTENAME(@TableName)

exec sp_executesql @sql

If however you want individual rows you could do something similar like this.

declare @sql nvarchar(max) = ''
    , @TableName sysname = 'YourTableName'

select @sql = @sql + 'select Name = ''' + c.name + ''', ValueCount = count(' + quotename(c.name) + '), ' + quotename(c.name) + ' = count(distinct ' + quotename(c.name) + ') from ' + quotename(@TableName) + ' union all '
from sys.columns c
where object_id = object_id(@TableName)

set @sql = left(@sql, len(@sql) - 10)

exec sp_executesql @sql

And last but not least if you really do want a new result set for each value as you indicated you can modify to something like this.

declare @sql nvarchar(max) = ''
    , @TableName sysname = 'YourTableName'

select @sql = @sql + 'select Name = ''' + c.name + ''', ValueCount = count(' + quotename(c.name) + '), ' + quotename(c.name) + ' = count(distinct ' + quotename(c.name) + ') from ' + quotename(@TableName) + ';'
from sys.columns c
where object_id = object_id(@TableName)

exec sp_executesql @sql