1

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'
  • What version of SQL Server you are using? – SqlWorldWide Sep 14 '17 at 17:43
  • I've tested on 2012 and 2016 – scsimon Sep 14 '17 at 17:45
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.