Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.spt_provider_types_view

  No additional text.


Syntax

create view sys.spt_provider_types_view
as
    select
        TYPE_NAME           = case when typeproperty(t.name, 'oldusertype') = 80 -- timestamp
                                  then t.name
                                  else d.type_name collate database_default
                              end,
        DATA_TYPE           = d.oledb_data_type,
        DATA_TYPE_90        = convert(smallint,
                                case -- Enable 9.0 downlevel clients to see new types.
                                when d.ss_dtype in (40, 41, 42, 43) -- date, time, datetime2, datetimeoffset
                                then 130 -- DBTYPE_WSTR
                                else d.oledb_data_type
                                end),
        DATA_TYPE_28        = convert(smallint,
                                case -- Enable ADO functionality by downgrading new Yukon types.
                                when d.oledb_data_type = 132 then 128 -- DBTYPE_UDT <--> DBTYPE_BYTES
                                when d.oledb_data_type = 141 then 130 -- DBTYPE_XML <--> DBTYPE_WSTR
                                when d.ss_dtype in (40, 41, 42, 43) -- date, time, datetime2, datetimeoffset
                                then 130 -- DBTYPE_WSTR
                                else d.oledb_data_type
                                end),
        COLUMN_SIZE         = case when d.oledb_data_type = 131 -- DBTYPE_NUMERIC
                                then @@max_precision
                                else coalesce(d.column_size,
                                              d.data_precision,
                                              case when t.system_type_id not in (34, 35, 99) -- ntext, image or text
                                              then typeproperty(t.name, 'precision')
                                              end
                                             )
                                end,
        COLUMN_SIZE_28      = case -- Enable ADO functionality by exposing XML size as NTEXT.
                                when d.oledb_data_type = 131 -- DBTYPE_NUMERIC
                                    then @@max_precision
                                when d.oledb_data_type = 141 -- DBTYPE_XML
                                    then 1073741823
                                else coalesce(d.column_size,
                                              d.data_precision,
                                              case when t.system_type_id not in (34, 35, 99) -- ntext, image or text
                                              then typeproperty(t.name, 'precision')
                                              end
                                             )
                                end,
        LITERAL_PREFIX      = d.literal_prefix,
        LITERAL_SUFFIX      = d.literal_suffix,
        CREATE_PARAMS       = convert(nvarchar(32),e.CREATE_PARAMS),
        CREATE_PARAMS_90    = convert(nvarchar(32),
                                case
                                when d.ss_dtype in (40, 41, 42, 43) -- date, time, datetime2, datetimeoffset
                                then null
                                else e.CREATE_PARAMS
                                end),
        IS_NULLABLE         = t.is_nullable,
        CASE_SENSITIVE      = convert (bit,
                                         case
                                         when d.oledb_data_type in (129,130) then -- DBTYPE_STR/DBTYPE_WSTR
                                            case
                                            when 'a' <> 'A' then 1
                                            else                 0
                                            end
                                         when d.oledb_data_type = 141 then 1 -- DBTYPE_XML is always case sensitive
                                         else 0
      end),
        SEARCHABLE          = d.searchable,
        UNSIGNED_ATTRIBUTE  = d.unsigned_attribute,
        FIXED_PREC_SCALE    = d.fixed_prec_scale,
        AUTO_UNIQUE_VALUE   = d.auto_unique_value,
        LOCAL_TYPE_NAME     = case
                              when typeproperty(t.name, 'oldusertype') = 80 then t.name -- timestamp
                              else d.localized_typename collate database_default
                              end,
        MINIMUM_SCALE       = convert(smallint,
                                        case
                                        when d.oledb_data_type = 131 then 0 -- DBTYPE_NUMERIC
                                        when t.system_type_id in (41,42,43) then 0 -- time/datetime2/datetimeoffset
                                        else null
                                        end),
        MINIMUM_SCALE_90    = convert(smallint,
                                        case
                                        when d.oledb_data_type = 131 then 0 -- DBTYPE_NUMERIC
                                        else null
                                        end),
        MAXIMUM_SCALE       = convert(smallint,
                                        case
                                        when d.oledb_data_type = 131 -- DBTYPE_NUMERIC
                                        then @@max_precision
                                        when t.system_type_id in (41,42,43) -- time/datetime2/datetimeoffset
                                        then typeproperty(t.name, 'scale')
                                        else null
                                        end),
        MAXIMUM_SCALE_90    = convert(smallint,
                                        case
                                        when d.oledb_data_type = 131 -- DBTYPE_NUMERIC
                                        then @@max_precision
                                        else null
                                        end),
        GUID                = convert(uniqueidentifier,null),
        TYPELIB             = convert(nvarchar(1),null),
        VERSION             = convert(nvarchar(1),null),
        IS_LONG             = d.is_long,
        BEST_MATCH          = convert(bit,
                              case
                              when typeproperty(t.name, 'oldusertype') = 80 then 0 -- timestamp
                              else d.best_match
                              end),
        BEST_MATCH_90       = convert(bit,
                              case
                              when typeproperty(t.name, 'oldusertype') = 80 then 0 -- timestamp
                              when t.system_type_id in (40, 41,42,43) then 0 -- date/time/datetime2/datetimeoffset
                              when t.system_type_id = 61 then 1 -- datetime
                              else d.best_match
                              end),
        BEST_MATCH_28       = convert(bit,
                              case
                              when typeproperty(t.name, 'oldusertype') = 80 then 0 -- timestamp
                              when d.oledb_data_type = 141 then 0 -- DBTYPE_XML
                              when t.system_type_id in (40, 41,42,43) then 0 -- date/time/datetime2/datetimeoffset
                              when t.system_type_id = 61 then 1 -- datetime
                              else d.best_match
                              end),
        IS_FIXEDLENGTH      = convert(bit, case when d.fixlen is null then 0 else 1 end),
        IS_FIXEDLENGTH_90   = convert(bit,
                              case
                              when d.ss_dtype in (40, 41, 42, 43) then 0 -- date, time, datetime2, datetimeoffset
                              when d.fixlen is null then 0
                              else 1 end),
        MANAGED_DATA_TYPE   = d.managed_type_code
    from
        sys.spt_provider_types d inner join
        sys.types t on
            (
                t.user_type_id = d.ss_usertype
            )
        left outer join
        sys.spt_datatype_info_ext e on
            (
                t.user_type_id = e.user_type and
                t.user_type_id < 256 and
                e.AUTO_INCREMENT = 0
            )

 
Last revision 2008RTM
See also

  sp_provider_types_100_rowset (Procedure)
sp_provider_types_90_rowset (Procedure)
sp_provider_types_rowset (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