SQL Server: retrieving data type length

Question

Hello,

I am writing a basic DBMS package and I am trying to retrieve the column length. This is the query I am using to retrieve the infomation:

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH 
FROM INFORMATION_SCHEMA.Columns 
WHERE TABLE_NAME = 'testVariables'

This works fine, no issues except for CHARACTER_MAXIMUM_LENGTH returns -1 all the time, When I use SQL Management Studio it says the column length is 16...

Any ideas?

Cheers, Joel

Answer 1

Take a look at look at books online and you will discover that the value -1 for CHARACTER_MAXIMUM_LENGTH means the data type is either XML of a large-value type.

See COLUMNS