What is the best way to get the count of the distinct values in each column of a table?
asked
Count of distinct values by column
If there are a few columns, simply do something like:
Select
count(distinct col1) as NumCol1,
count(distinct col2) as NumCol2
from
tableName
If there numerous columns then you can use dynamic sql to get every single one of them, for instance:
declare @sql varchar(max)
declare @tablename varchar(128)
set @tableName = 'TestTable'
select @sql = 'select' + char(10)
select @sql = @sql + ' count(distinct ' + Column_name + ') as Num' + Column_name + ', ' + char(10)
from information_schema.columns
where table_name = @tableName
--remove the last comma
set @sql = substring(@sql, 1, len(@sql) - 3) + char(10)
select @sql = @sql + 'from ' + @tableName
print @sql
exec (@sql)
