Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.spt_datatype_info_view

 

This is a view in the mssqlsystemresource database.




Syntax

create view sys.spt_datatype_info_view
as
    select
        ODBCVer             = d.ODBCVer,
        TYPE_NAME           = convert(sysname,
                                      case
                                        when (t.system_type_id = 240 or t.user_type_id > 255) then t.name
                                        else d.TYPE_NAME collate database_default
                                      end),
        DATA_TYPE           = d.DATA_TYPE,
        DATA_TYPE_90        = convert(smallint,
                                      case
                                        when d.ss_dtype in (40,41,42,43) then -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                          -9  -- SQL_WVARCHAR
                                        else
                                          d.DATA_TYPE
                                      end),
        DATA_TYPE_28        = convert(smallint, -- backward-compatible ODBC types
                                      case
                                        when (d.ss_dtype = 241) then -- XML
                                          -10
                                        when d.ss_dtype in (40, 41, 42, 43) then -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                          -9  -- SQL_WVARCHAR
                                        else
                                          d.DATA_TYPE
                                      end),
        PRECISION           = convert(int,
                                      case
                                        when (d.DATA_TYPE in (6,7,-150)) or (d.ss_dtype = 241)
                                          then d.data_precision   -- FLOAT/REAL/sql_variant/XML for Yukon+
                                        when d.ss_dtype in (106,108) and t.user_type_id <= 255
                                          then @@max_precision    -- DECIMAL/NUMERIC
                                        else OdbcPrec(t.system_type_id, t.max_length, t.precision)
                                      end),
        PRECISION_28        = convert(int,
                                      case
                                        when (d.DATA_TYPE in (6,7,-150))
                                          then d.data_precision   -- FLOAT/REAL/sql_variant
                                        when (d.ss_dtype = 241)
                                          then 1073741823         -- XML is the same as ntext for pre-Yukon clients
                                        when d.ss_dtype in (106,108) and t.user_type_id <= 255
                                          then @@max_precision    -- DECIMAL/NUMERIC
                                        else OdbcPrec(t.system_type_id, t.max_length, t.precision)
                                      end),
        LITERAL_PREFIX      = d.LITERAL_PREFIX,
        LITERAL_SUFFIX      = d.LITERAL_SUFFIX,
        CREATE_PARAMS       = e.CREATE_PARAMS,
        CREATE_PARAMS_90    = convert(varchar(32),
                                      case
                                        when d.ss_dtype in (40,41,42,43) then
                                          null -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                        else
                                          e.CREATE_PARAMS
                                      end),
        NULLABLE            = convert(smallint,
                                      case
                                        when d.AUTO_INCREMENT = 1 then 0 -- IDENTITY
                                        else TypeProperty (t.name, 'AllowsNull')
                                      end),
        CASE_SENSITIVE      = convert (smallint,
                                       case
                                         when d.DATA_TYPE in (1, -1, 12, -8, -9, -10) then -- char/text/varchar/nchar/nvarchar/ntext
                                            case
                                         when 'a' <> 'A' then 1
                                              else 0
                                            end
                                         when d.DATA_TYPE = -152 then 1 -- SQL_SS_XML is always case sensitive
                                         else 0
                                       end),
        CASE_SENSITIVE_28   = convert (smallint,
                                       case
                                         when d.ss_dtype = 241 then 1 -- SQL_SS_XML is always case sensitive
                                         when d.DATA_TYPE in (1, -1, 12, -8, -9, -10) then -- char/text/varchar/nchar/nvarchar/ntext
                                           case
                                             when 'a' <> 'A' then 1
                                             else 0
                                           end
                                         else 0
                                       end),
        SEARCHABLE          = d.SEARCHABLE,
        UNSIGNED_ATTRIBUTE  = d.UNSIGNED_ATTRIBUTE,
        MONEY               = d.MONEY,
        AUTO_INCREMENT      = convert(smallint,
                                      case -- money/float/real/tinyint/smallmoney/smallint/int/decimal/numeric/bigint
                                        when d.ss_dtype in (60, 62, 59, 122, 48, 52, 56, 106, 108, 127)
                                        then d.AUTO_INCREMENT
                                        else null -- not applicable for other types
                                      end),
        LOCAL_TYPE_NAME     = convert(sysname,
                                      case
                                        when (t.system_type_id = 240 or t.user_type_id > 255) then t.name
                                        else d.LOCAL_TYPE_NAME collate database_default
                                      end),
        MINIMUM_SCALE       = convert(smallint,
                                      case
                                        when d.ss_dtype in (106,108) and t.user_type_id > 255
                                          then TypeProperty (t.name, 'Scale')
                                        when d.ss_dtype in (41,42,43)
                                          then 0 -- TIME2/DATETIME2/DATETIMEOFFSET
                                        else d.numeric_scale
                                      end),
        MINIMUM_SCALE_90    = convert(smallint,
                                      case
                                        when d.ss_dtype in (106,108) and t.user_type_id > 255
                                          then TypeProperty (t.name, 'Scale')
                                        when d.ss_dtype in (40,41,42, 43)
                                          then null -- DATE/TIME2/DATETIME2/DATETIMEOFFSET
                                        else d.numeric_scale
                                      end),
        MAXIMUM_SCALE       = convert(smallint,
                                      case
                                        when d.ss_dtype in (106,108) and d.AUTO_INCREMENT = 0 and t.user_type_id <= 255
                                          then @@max_precision -- DECIMAL/NUMERIC
                                        when (d.ss_dtype in (106,108) and d.AUTO_INCREMENT = 1) or (d.ss_dtype in (98, 104))
                                          then 0 -- DECIMAL/NUMERIC IDENTITY/SQL_VARIANT/BIT
                                        else TypeProperty (t.name, 'Scale')
                                      end),
        MAXIMUM_SCALE_90    = convert(smallint,
                                      case
                                        when d.ss_dtype in (106,108) and d.AUTO_INCREMENT = 0 and t.user_type_id <= 255
                                          then @@max_precision -- DECIMAL/NUMERIC
           when (d.ss_dtype in (106,108) and d.AUTO_INCREMENT = 1) or (d.ss_dtype in (98, 104))
                                          then 0 -- DECIMAL/NUMERIC IDENTITY/SQL_VARIANT/BIT
                                        when d.ss_dtype in (40,41,42,43)
                                          then null -- DATE/TIME2/DATETIME2/DATETIMEOFFSET
                                        else TypeProperty (t.name, 'Scale')
                                      end),
        SQL_DATA_TYPE       = d.SQL_DATA_TYPE,
        SQL_DATA_TYPE_90    = convert(smallint,
                                      case
                                        when d.ss_dtype in (40,41,42,43) -- DATE/TIME2/DATETIME2/DATETIMEOFFSET
                                          then  -9  -- SQL_WVARCHAR
                                        else
                                          d.SQL_DATA_TYPE
                                      end),
        SQL_DATA_TYPE_28    = convert(smallint, -- backward-compatible ODBC types
                                      case
                                        when (d.ss_dtype = 241) then -- XML
                                          -10
                                        when d.ss_dtype in (40, 41, 42, 43)
                                          then  -9  -- SQL_WVARCHAR -- DATE/TIME2/DATETIME2/DATETIMEOFFSET
                                        else
                                          d.SQL_DATA_TYPE
                                      end),
        SQL_DATETIME_SUB    = d.SQL_DATETIME_SUB,
        SQL_DATETIME_SUB_90 = convert(smallint,
                                      case
                                        when d.ss_dtype in (40,41,42,43)
                                          then null -- DATE/TIME2/DATETIME2/DATETIMEOFFSET
                                        else
                                          d.SQL_DATETIME_SUB
                                      end),
        NUM_PREC_RADIX      = convert(int,d.RADIX),
        INTERVAL_PRECISION  = convert(smallint,NULL),
        USERTYPE            = convert(smallint, typeproperty(t.name, 'oldusertype'))

    from
        sys.spt_datatype_info d inner join
        sys.types t on
            (
                -- We have to return all system types and SQL UDTs (as Shiloh does),
                -- but no CLR UDTs.
                d.ss_dtype = t.system_type_id and
                (
                    d.ss_usertype = t.user_type_id or -- system UDTs & regular types
                    (
                        t.system_type_id <> 240 and t.user_type_id > 255 and -- SQL UDTs
                        d.ss_dtype <> 240       and d.ss_usertype = d.ss_dtype
                    )
                )
            )
        left outer join
        sys.spt_datatype_info_ext e on
            (
                t.user_type_id = e.user_type and
                d.AUTO_INCREMENT = e.AUTO_INCREMENT
            )
    where
        (t.user_type_id <= 255 or d.AUTO_INCREMENT = 0) and
        d.DATA_TYPE <> -153 -- exclude table types

 
Last revision 2008RTM
See also

  sp_datatype_info (Procedure)
sp_datatype_info_100 (Procedure)
sp_datatype_info_90 (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash