SQL Server – Why sp_columns / sys.spt_columns_odbc_view Returns Incorrect Results

sql server

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 & 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'

Best Answer

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 > 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

enter image description here

Related Question