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 =
     Replace(
       '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'
,'
','')

PRINT(@SqlString)

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 


Add comment

Security code
Refresh