How to count number of distinct values in each column of a table in MS-SQL

SQL_icon_base.jpgAre you looking for simple way, how to get count of distinct values for each column in a table on MS-SQL database? There is a simple script with solution to help you.


Easily use this query to generate SQL statement for you with distinct count for each value in a table. Script is based on information schema, to get list of all columns in table


Replace string REPLACE_BY_TABLE_NAME to your table name

DECLARE @SqlString varchar(max) 

SELECT @SqlString =
       'SELECT ' + SUBSTRING((SELECT ',' +'COUNT(DISTINCT(' + column_name + ')) As '
       + column_name + ' ' + CHAR(13) + CHAR(10)
       FROM information_schema.columns
       WHERE table_name = 'REPLACE_BY_TABLE_NAME'
       for xml path('')),2,200000) + 'FROM REPLACE_BY_TABLE_NAME'


When you run the script, you will get SQL query as result. Copy this query to new query window and run it.

Solution is slightly modified version from discussion on StackOverflow SQL Server count number of distinct values in each column of a table 

Author info
Author: Stanislav DubenWebsite:
About me
I am experienced database engineer with more than 12 years developing and optimization experience. I worked on many high level projects based on SQL servers. I am also photograper and owner of many internet projects.

Add comment

Security code