list possible values for the DATA_TYPE column in INFORMATION_SCHEMA.COLUMNS view

Question

list possible values for the DATA_TYPE column in INFORMATION_SCHEMA.COLUMNS view

Bill Nicolich

SSCommitted

Points: 1865
More actions
April 28, 2010 at 2:13 pm

#218338

The INFORMATION.SCHEMA.COLUMNS view has a column called DATA_TYPE. BOL doesn't list all the potential values. Does anyone know of one? I do need that list for a project I'm working on.
The DATA_TYPE column lists the system-supplied data type name of a table column. The DATA_TYPE column is an nvarchar(128) column. So the data type names are character names such as "nvarchar", "varchar" and so forth.
With a list of these values, one could do some conditional matching - which is what I intend to do.
So, if anyone knows of a comprehensive list of these values, that's what I'm looking for. Thanks!
Bill N.
Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

The nvarchar(128) is the defined type and length for identifiers (see BOL, section "identifiers [SQL Server]")

I would use the allowed tpes as described for base_types when creating a user-defined type (BOL, section "create type"). One thing I'm not sure about: whether the name of the user-defined data type is stored in this table or the base type (I'd expect the base_type but I would test it first if needed...).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 2

SELECT name

FROM sys.types;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Bill Nicolich SSCommitted Points: 1865 More actions · Answer 3

Paul White NZ (4/29/2010)
SELECT name
FROM sys.types;

Paul: That looks right! How did you find it?

Apparently the data type values themselves aren't specified in the ISO standard - so, vendors will differ. For instance, Microsoft uses the bit column which is vendor-specific.

Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow