Why does sp_columns / sys.spt_columns_odbc_view return incorrect results? While investigating a question on Stackoverflow, I discovered that sp_columns, which uses sys.spt_columns_odbc_view, is returning the wrong / unexpected DATA_TYPE &amp; TYPE_NAME for NVARCHAR(MAX). It's returning NTEXT for NVARCHAR columns and I can't figure out why. Does anyone have any insight?

DEMO

CREATE TABLE [dbo].[deleteTable](
    [v] [nvarchar](max) NULL,
    [t] [ntext] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

select 
    c.name
    ,c.system_type_id
    ,ty.name
    ,ty.precision
    ,t.name
from 
    sys.columns c
inner join 
    sys.tables t on t.object_id = c.object_id
inner join 
    sys.types ty on ty.system_type_id = c.system_type_id
where
t.name = 'deleteTable'

exec sp_columns @table_name = 'deleteTable'
I figured out why you get NTEXT but do not know why this is done. This note may be relevant:


  sp_datatype_info is equivalent to SQLGetTypeInfo in ODBC. The results
  returned are ordered by DATA_TYPE and then by how closely the data
  type maps to the corresponding ODBC SQL data type.


In the definition of sys.spt_columns_odbc_view there is a piece of code:

TYPE_NAME = CONVERT(sysname, 
CASE 
WHEN ( 
    t.system_type_id = 240 
    OR 
    t.user_type_id &gt; 255 
  ) 
  THEN -- CLR UDTs 
  t.NAME 
  ELSE d.type_name COLLATE database_default 
END 
), sys.types t ON ( 
  t.user_type_id = c.user_type_id 
) 
INNER JOIN sys.spt_datatype_info d ON ( 
  d.ss_dtype = c.system_type_id 
  AND 
  d.auto_increment = c.is_identity 
)


In your case ELSE d.type_name COLLATE database_default this is being executed because the data type is -10.

sys.spt_datatype_info uses sp_datatype_info.

If you execute below code you will see where NTEXT is coming from.

EXEC sp_datatype_info -10;  
GO