get a count of each value from every column in a table SQL Server

Question

So I looked this up and this question is very similar but it's missing a key piece: SQL Server count number of distinct values in each column of a table

So in that question they want the distinct count for each column. What I am looking to do is to get a count of each distinct value for each column in a table (and I'm doing this for all the tables in a particular database which is why I'm looking to try to automate this as much as possible). Currently my code looks like this which I have to run for each column:

select mycol1, COUNT(*) as [Count] 
from mytable
group by mycol1
order by [Count] desc

Ideally my output would look like this:

ColumnName1 Count
val1        24457620
val2        17958530
val3        13350

ColumnName2 Count
val1        24457620
val2        17958530
val3        13350
val4 12

and so on for all the columns in the table

This answer below (provided by @beargle) from that previous question is really close to what I'm looking to do but I can't seem to figure out a way to get it to work for what I am trying to do so I would appreciate any help.

DECLARE @Table SYSNAME = 'TableName'; 

-- REVERSE and STUFF used to remove trailing UNION in string
SELECT REVERSE(STUFF(REVERSE((SELECT 'SELECT ''' + name 
                                     + ''' AS [Column], COUNT(DISTINCT(' 
                                     + QUOTENAME(name) + ')) AS [Count] FROM ' 
                                     + QUOTENAME(@Table) + ' UNION ' 
                              -- get column name from sys.columns  
                              FROM   sys.columns 
                              WHERE  object_id = Object_id(@Table)
                              -- concatenate result strings with FOR XML PATH
                              FOR XML PATH (''))), 1, 7, ';'));

GarethD · Accepted Answer · 2014-01-23T16:55:23.107

You could use:

DECLARE @Table SYSNAME = 'TableName'; 
DECLARE @SQL NVARCHAR(MAX) = ''
SELECT @SQL = STUFF((SELECT ' UNION SELECT ''' + name 
                            + ''' AS [Column], ' 
                            + 'CAST(' + QUOTENAME(Name)
                            + ' AS NVARCHAR(MAX)) AS [ColumnValue], COUNT(*) AS [Count] FROM ' 
                            + QUOTENAME(@Table) + ' GROUP BY ' + QUOTENAME(Name)
                    FROM   sys.columns 
                    WHERE  object_id = Object_id(@Table)
                    -- concatenate result strings with FOR XML PATH
                    FOR XML PATH ('')), 1, 7, '');

EXECUTE sp_executesql @SQL;

Which will produce SQL Like the following for a table with two columns (Column1 and Column2)

SELECT 'Column1' AS [Column], 
        CAST([Column1] AS NVARCHAR(MAX)) AS [ColumnValue], 
        COUNT(*) AS [Count] 
FROM    [TableName] 
GROUP BY [Column1] 
UNION 
SELECT  'Column2' AS [Column], 
        CAST([Column2] AS NVARCHAR(MAX)) AS [ColumnValue], 
        COUNT(*) AS [Count] 
FROM    [TableName] 
GROUP BY [Column2]

EDIT

If you want a new result set for each column then use:

DECLARE @Table SYSNAME = 'TableName'; 
DECLARE @SQL NVARCHAR(MAX) = '';
SELECT @SQL = (SELECT ' SELECT ' + QUOTENAME(Name) 
                        + ', COUNT(*) AS [Count] FROM ' 
                        + QUOTENAME(@Table) + ' GROUP BY ' + QUOTENAME(Name) + ';'
                FROM   sys.columns 
                WHERE  object_id = Object_id(@Table)
                -- concatenate result strings with FOR XML PATH
                FOR XML PATH (''));

EXECUTE sp_executesql @SQL;

Which would produce SQL Like:

SELECT  [Column1], 
        COUNT(*) AS [Count] 
FROM    [callsupplier] 
GROUP BY [Column1];

SELECT  [Column2], 
        COUNT(*) AS [Count] 
FROM    [callsupplier] 
GROUP BY [Column2];

Let me suggest `UNION ALL` instead of just `UNION`, even though SQL Server could infer it doesn't matter. — Michel de Ruiter, Apr 21 '16 at 09:05

RoughPlace · Answer 2 · 2014-01-23T16:52:10.657

thought i would take a stab at this whilst waiting for a backup to restore

hope this does what you require

create Table #Temp 
(tableName varchar(100),
columnName varchar(100),
value varchar(1000),
distinctItems int)

Declare @tabName as varchar(100)
Declare @colName as varchar(100)
Declare @tabid as int

Declare cursorTables Cursor
for 
select t.object_id , t.name , c.name from sys.tables t inner join sys.columns c on     t.object_id = c.object_id

open cursorTables 
Fetch Next from cursorTables into 
@tabid,@tabName,@colName


while @@Fetch_Status = 0 
Begin

declare @query as nVarchar(1000)
set @query  = 'Insert into #Temp SELECT    ''' + @tabName + '''  , '''+ @colName +''', '     + @colName + ',  COUNT([' + @colName +']) AS Expr1 FROM          [' + @tabName+ '] group by     [' + @colName + ']'
print @query
exec sp_executesql @query

Fetch Next from cursorTables into 
@tabid,@tabName,@colName


End 

Close cursorTables
Deallocate cursorTables

select * from #temp

drop table #temp

produces some not very useful results on PK values and i suspect it would not work on columns greater than varchar(1000) but works on a fe of my dbs

score 0 · Answer 3 · answered Oct 03 '17 at 22:30

This version makes a good snippet:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += 'SELECT ''' + t.name + ''', ''' + c.name + ''', ' + c.name + ', COUNT(' + c.name + ') AS C FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ' GROUP BY ' + c.name + ';' + CHAR(13)
    FROM sys.tables AS t
    INNER join sys.columns c on t.object_id = c.object_id
    INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] 
    WHERE s.name LIKE 'stage' AND t.name LIKE 'table' AND c.name LIKE '%whatever%';

--PRINT @sql;
EXEC sp_executesql @sql

get a count of each value from every column in a table SQL Server

3 Answers3

Linked