Please rate this item: 1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

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

  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 *

CJayMead's SQL Server, Endeca, MS Office Help © 2018