SQL: Count of values within every field in a table

The Transact-SQL script below (written for SQL Server 2005) 1, returns a list of the values and a count for each and every field within a table. You just need to change the schema name and table name, and specify a limit (if needed) for the number of values returned for each field.

For example:

ID FieldName FieldValue Count
1 Field1 Most used value 1000
2 Field1 Second most used value 750
10 Field2 Most used value for Field2 250
CREATE TABLE TEMP_TableColumnCounts
( ID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
  FieldName VARCHAR(100),
  FieldValue VARCHAR(8000),
  FieldCount INT
)

DECLARE @SchemaName VARCHAR(100)
DECLARE @TableName VARCHAR(100)
DECLARE @ColumnName VARCHAR(100)
DECLARE @TopXCount INT
DECLARE @SQL VARCHAR(1000)

--Specify the schema name, and table name
SET @SchemaName = 'dbo'
SET @TableName = 'tablename'

--If you only want to show the top 10 counts, change the 0 to 10.
--To show all counts, specify zero.
SET @TopXCount = 0

DECLARE ColumnCursor CURSOR FAST_FORWARD FOR
	SELECT C.name 
	FROM SYS.COLUMNS C
	INNER JOIN SYS.TABLES T
		ON C.object_id = T.object_id
	WHERE SCHEMA_NAME(T.schema_id) = @SchemaName
	AND T.name = @TableName
	ORDER BY C.column_id

OPEN ColumnCursor

FETCH NEXT FROM ColumnCursor
	INTO @ColumnName

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @SQL = 'INSERT INTO TEMP_TableColumnCounts (FieldName, FieldValue, FieldCount)'
	SET @SQL = @SQL + ' SELECT'
	IF @TopXCount > 0
		SET @SQL = @SQL + ' TOP ' + LTRIM(RTRIM(STR(@TopXCount)))
	SET @SQL = @SQL + ' ' + CHAR(39) + @ColumnName + CHAR(39) + ' AS col,'
	SET @SQL = @SQL + ' [' + @Columnname + '] AS colval,'
	SET @SQL = @SQL + ' COUNT(*) as cnt'
	SET @SQL = @SQL + ' FROM [' + @SchemaName + '].[' + @TableName + '] '
	SET @SQL = @SQL + ' GROUP BY [' + @ColumnName + ']'
	IF @TopXCount > 0
		SET @SQL = @SQL + ' ORDER BY COUNT(*) DESC'
	EXEC (@SQL)
	FETCH NEXT FROM ColumnCursor
		INTO @ColumnName
END

CLOSE ColumnCursor
DEALLOCATE ColumnCursor

SELECT * FROM TEMP_TableColumnCounts

DROP TABLE TEMP_TableColumnCounts
  1. Please ensure you test any script taken from my website on a test/development machine, before running on a production server.

2 Comments

Add a Comment
  1. Hello,

    Excellent code set. I have been searching for a way to do this and this has been a tremendous help.

    However, I have experienced difficulty utilizing the @TopXCount variable to narrow down the number of each field that I am working with. It is that it simply does not work. This may be a flaw in the code, or something to do with my use of MS SQL Server 2008 instead of 2005…I don’t know. Any insight as to why this might not be working?

    Thanks again for your work. Even without the ability to get it down to top 100 or something it is great.

  2. Hi Jamie.

    If you change
    SET @TopXCount = 0
    to:
    SET @TopXCount = 100

    You should get the 100 most common values for each column in the table.

    Do please let me know if this helps.

    Kind regards
    C


    Jamie:

    Hello,
    Excellent code set. I have been searching for a way to do this and this has been a tremendous help.
    However, I have experienced difficulty utilizing the @TopXCount variable to narrow down the number of each field that I am working with. It is that it simply does not work. This may be a flaw in the code, or something to do with my use of MS SQL Server 2008 instead of 2005…I don’t know. Any insight as to why this might not be working?
    Thanks again for your work. Even without the ability to get it down to top 100 or something it is great.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.