SQL Server: How to check column data types in SQL Server database?

There are at least a few ways to get column data types in Microsoft SQL Server database. In this short post, I will show you two the most popular ways and explain you the difference between MAX_LENGH (CHARACTER_OCTET_LENGTH) and CHARACTER_MAXIMUM_LENGTH columns.

Checking data types

The first way to check data types for SQL Server database is query with SYS schema table. The below query uses COLUMNS and TYPES tables:

SELECT C.NAME AS COLUMN_NAME,
       TYPE_NAME(C.USER_TYPE_ID) AS DATA_TYPE,
       C.IS_NULLABLE,
       C.MAX_LENGTH,
       C.PRECISION,
       C.SCALE
FROM SYS.COLUMNS C
JOIN SYS.TYPES T
     ON C.USER_TYPE_ID=T.USER_TYPE_ID
WHERE C.OBJECT_ID=OBJECT_ID('your_table_name');

The other way to check data types is the statement with using INFORMATION_SCHEMA database. In the below statement you need COLUMNS table:

SELECT COLUMN_NAME,
       DATA_TYPE,
       IS_NULLABLE,
       CHARACTER_MAXIMUM_LENGTH,
       NUMERIC_PRECISION,
       NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='your_table_name';

If you try use the both of these queries, you can see that for some attributes MAX_LENGTH have bigger value than CHARACTER_MAXIMUM_LENGTH for the second query. But why?

Difference between MAX_LENGH and CHARACTER_MAXIMUM_LENGTH

The MAX_LENGTH attributes from SYS.COLUMNS table stores maximum length in bytes, whereas CHARACTER_MAXIMUM_LENGTH stores maximum length in characters. For attributes with char or varchar data types is does not matter, but for nchar or nvarchar it is. MAX_LENGTH attribute will be the same like CHARACTER_OCTET_LENGTH at INFORMATION_SCHEMA.COLUMNS table.

If you enjoyed this post please add the comment below or share this post on your Facebook, Twitter, LinkedIn or another social media webpage.
Thanks in advanced!

Leave a Reply

avatar
  Subscribe  
Notify of
Close Menu