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!