Enterprise Manager uses sp_MShelpcolumns to return the column info
and not sp_help. sp_help uses the length in syscolumns.length which is in
bytes wheras sp_MShelpcolumns converts it into number of characters for
text strings and divides by 2 if its nchar or nvarchar. Thus sp_help is
you the byte length and sp_MShelpcolumns is showing you the length in
select len(N'12345') as 'Charlength',
datalength(N'12345') as 'Bytelength'
The below is the note at the top of sp_MShelpcolumns which explains :
/* For non-string columns, sp_MShelpcolumns returns the length in
/* which is defined in BOL as "maximum physical storage length from
/* For string columns (including types based on string types),
/* returns this maximum length in characters (i.e. it returns
/* adjusted to whether the column is based on char or nchar).
> Using SQL Server 2000. When I open Enterprise Manager and select "tables"
> and then right click on a table and choose "design" I see the field types
> and sizes. These match the sizes in the Access 2000 table from which the
> table was imported. If I open Query analyzer and run the query "sp_help
> 'table_name'" all of the text fields (nvarchar is SQL Server) are shown
> a length double what they should be?
> Which size is true? Why are they different?
> Wayne Wengert