SQL: Table information – column lengths, counts and unique counts

Hi.

The Transact-SQL script below 1 returns the following information:

  • ID – Just an identity column used within the script
  • SchemaName – The name of the schema
  • TableName – Obviously, the name of the table 🙂
  • ColumnName
  • Type – The data type of the column
  • Length – The maximum number of bytes used by the field
  • MaxLength – The maximum number of bytes currently used (referring to the data)
  • ValueCount – The number of records that have a not NULL value
  • ValueUniqueCount – The number of unique not NULL values

You can’t get any length or count information from “hierarchyid”, “geometry” or “geography” fields (SQL Server 2008 specific data types), so these are returned as NULL.

As SQL Server 2000 doesn’t use Schema Names, I have adapted the above code to work with SQL Server 2000:

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

1 Comment

Add a Comment
  1. I have just amended the code above for SQL Server 2005 to use the user_type_id instead of system_type_id. This will give better results with columns not being duplicated with nvarchar AND sysname. I have also included the DATALENGTH function for returning the length of text fields.

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.

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