I am retrieving column properties of a table with the following query
c.name as ColumnName, type_name(c.system_type_id) as ColumnType,
c.is_nullable as AllowNull,
object_name(fkc.referenced_object_id) as RefTable,
col_name(fkc.referenced_object_id,fkc.referenced_column_id) as RefColumn,
OBJECTPROPERTY(OBJECT_ID(kcu.constraint_name), 'IsPrimaryKey') as IsPK,
max_length as Length
from sys.tables as t
inner join sys.columns c on c.object_id=t.object_id
left outer join sys.foreign_key_columns fkc on fkc.parent_column_id=c.column_id and fkc.parent_object_id=c.object_id
left outer join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu on kcu.COLUMN_NAME=c.name and kcu.TABLE_NAME=object_name(c.object_id)
which returns CityCode column's length as 50.
The thing is the actual length of the CityCode column in the table is 25 (nvarchar(25)).
I checked it with other tables and max_length property always returns the double of the actual size. I dont need the other columns, just columns with nvarchar
I read the documentation of max_length and it says:
Maximum length (in bytes) of the column.
-1 = Column data type is varchar(max), nvarchar(max), varbinary(max), or xml.
For text columns, the max_length value will be 16 or the value set by sp_tableoption 'text in row'.
Is dividing the max_length by 2 to retrieve original value reliable, or is there another way?
Thanks in advance.