Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.spt_columns_odbc_view

 

spt_columns_odbc_view




Syntax

create view sys.spt_columns_odbc_view
as
    select
    -- begin (for doing joins)
        OBJECT_ID           = o.object_id,
        SCHEMA_ID           = o.schema_id,
        ODBCVER             = d.ODBCVer,
    -- end (for doing joins)
        TABLE_QUALIFIER     = convert(sysname,DB_NAME()),
        TABLE_OWNER         = convert(sysname,schema_name(o.schema_id)),
        TABLE_NAME          = convert(sysname,o.name),
        COLUMN_NAME         = convert(sysname,c.name),
        DATA_TYPE           = d.DATA_TYPE,
        DATA_TYPE_90	    = convert(smallint, -- new date time types for downlevel client
                                case
                                when d.ss_dtype IN (40, 41, 42, 43) then -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                    -9  -- SQL_WVARCHAR
                                when (d.ss_dtype = 240 and c.max_length = -1) then -- Large UDT => varbinary(max) for 9.0 Client
                                    -3
                                else
                                    d.DATA_TYPE
                                end),
        DATA_TYPE_28        = convert(smallint, -- backward-compatible ODBC types
                                case
                                when (d.ss_dtype = 240) then -- CLR UDT
                                    -4
                                when (d.ss_dtype = 241) then -- XML
                                    -10
                                when (c.max_length = -1 and d.ss_dtype = 167) then -- varchar(max)
                                    -1
                                when (c.max_length = -1 and d.ss_dtype = 231) then -- nvarchar(max)
                                    -10
                                when (c.max_length = -1 and d.ss_dtype = 165) then -- varbinary(max)
                                    -4
                                when d.ss_dtype IN (40, 41, 42, 43) then -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                     -9  -- SQL_WVARCHAR
                                else
                                    d.DATA_TYPE
                                end),
        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),
        TYPE_NAME_28        = convert(sysname,
                                case
                                when (t.system_type_id = 240 or t.user_type_id > 255) then -- CLR UDTs
                                    t.name
                                when (c.max_length = -1 and d.ss_dtype = 167) then -- varchar(max)
                                    N'text'
                                when (c.max_length = -1 and d.ss_dtype = 231) then -- nvarchar(max)
                                    N'ntext'
                                when (c.max_length = -1 and d.ss_dtype = 165) then -- varbinary(max)
                                    N'image'
                                else
                                    d.TYPE_NAME
                                end) collate database_default,
        "PRECISION"         = convert(int,
                                case
                                when d.DATA_TYPE in (6,7) then -- FLOAT/REAL
                                    d.data_precision
                                when c.max_length = -1 then
                                    0
                                else
                                    OdbcPrec(c.system_type_id,c.max_length,c.precision)
                                end),
        PRECISION_28        = convert(int,
                                case
when d.DATA_TYPE in (6,7) then -- FLOAT/REAL
                                    d.data_precision
                                when d.ss_dtype = 241 then -- XML
                                    1073741823
                                when (c.max_length = -1 and d.ss_dtype = 167) then -- varchar(max)
                                    2147483647
                                when (c.max_length = -1 and d.ss_dtype = 231) then -- nvarchar(max)
                                    1073741823
                                when (c.max_length = -1 and d.ss_dtype = 165) then -- varbinary(max)
                                    2147483647
                                when (c.max_length = -1 and d.ss_dtype = 240) then -- Large UDT => image for non-SNAC clients
                                    2147483647
                                else
                                    OdbcPrec(c.system_type_id,c.max_length,c.precision)
                                end),
        "LENGTH"            = convert(int,
                                case
                                when d.ss_dtype IN (108,106) then    -- decimal/numeric types
                                    OdbcPrec(c.system_type_id,c.max_length,c.precision)+2
                                when c.max_length = -1 then -- Large UDT => Unlimited Length like varbinary(max)
                                    0
                                when d.ss_dtype = 240 then -- Small UDT
                                    isnull(u.max_length, c.max_length)
                                else
                                    isnull(d.length, c.max_length)
                                end),
        LENGTH_90            = convert(int,
                                case
                                when d.ss_dtype IN (108,106) then    -- decimal/numeric types
                                    OdbcPrec(c.system_type_id,c.max_length,c.precision)+2
                                when d.ss_dtype = 240 then -- CLR UDTs
                                    isnull(u.max_length, c.max_length)
                                when c.max_length = -1 then
                                    0
                                when d.ss_dtype IN (40, 41, 42, 43) then -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                    OdbcPrec(c.system_type_id,c.max_length,c.precision)*2
                                else
                                    isnull(d.length, c.max_length)
                                end),
        LENGTH_28           = convert(int,
                                case
                                when d.ss_dtype IN (108,106) then    -- decimal/numeric types
                                    OdbcPrec(c.system_type_id,c.max_length,c.precision)+2
                                when (c.max_length = -1 and d.ss_dtype = 240) then -- Large UDT => image for non-SNAC clients
                                    2147483647
                                when d.ss_dtype = 240 then -- Small UDT
                                    isnull(u.max_length, c.max_length)
                                when d.ss_dtype = 241 then -- XML
                                    2147483646
                                when (c.max_length = -1 and d.ss_dtype = 167) then -- varchar(max)
                                    2147483647
                                when (c.max_length = -1 and d.ss_dtype = 231) then -- nvarchar(max)
                                    2147483646
                                when (c.max_length = -1 and d.ss_dtype = 165) then -- varbinary(max)
                                    2147483647
                                when d.ss_dtype IN (40, 41, 42, 43) then -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                    OdbcPrec(c.system_type_id,c.max_length,c.precision)*2
                                else
  isnull(d.length, c.max_length)
                                end),
        SCALE               = convert(smallint, OdbcScale(c.system_type_id,c.scale)),
        SCALE_90            = convert(smallint,
                                case
                                when d.ss_dtype IN (40, 41, 42, 43) then  -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                    null
                                else
                                    OdbcScale(c.system_type_id,c.scale)
                                end),
        RADIX               = d.RADIX,
        NULLABLE            = convert(smallint, ColumnProperty (c.object_id, c.name, 'AllowsNull')),
        REMARKS             = convert(varchar(254),null),   -- Remarks are NULL
        COLUMN_DEF          = convert(nvarchar(4000), object_definition(ColumnProperty(c.object_id, c.name, 'default'))),
        SQL_DATA_TYPE       = d.SQL_DATA_TYPE,
        SQL_DATA_TYPE_90    = convert(smallint,
                                case
                                when d.ss_dtype IN (40, 41, 42, 43) then  -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                     -9  -- SQL_WVARCHAR
                                when (d.ss_dtype = 240 and c.max_length = -1) then -- Large UDT => varbinary(max) for 9.0 clients
                                    -3
                                else
                                    d.SQL_DATA_TYPE
                                end),
        SQL_DATA_TYPE_28    = convert(smallint, -- backward-compatible ODBC types
                                case
                                when (d.ss_dtype = 240) then -- CLR UDT
                                    -4
                                when (d.ss_dtype = 241) then -- XML
                                    -10
                                when (c.max_length = -1 and d.ss_dtype = 167) then -- varchar(max)
                                    -1
                                when (c.max_length = -1 and d.ss_dtype = 231) then -- nvarchar(max)
                                    -10
                                when (c.max_length = -1 and d.ss_dtype = 165) then -- varbinary(max)
                                    -4
                                when d.ss_dtype IN (40, 41, 42, 43) then  -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                     -9  -- SQL_WVARCHAR
                                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 -- DATE/TIME/DATETIME2/DATETIMEOFFSET
                                    null
                                else
                                    d.SQL_DATETIME_SUB
                                end),
        CHAR_OCTET_LENGTH   = convert(int,
                                case
                                when c.max_length = -1 then -- Large UDT => Unlimited length like varbinary(max)
                                    0
                                when d.ss_dtype = 240 then -- Small UDT
                                    isnull(u.max_length, c.max_length)
                                else
                                    isnull(d.length, c.max_length)+d.charbin
                                end),
        CHAR_OCTET_LENGTH_28= convert(int,
                                case
                                when (c.max_length = -1 and d.ss_dtype = 240) then -- Large UDT => image for non-SNAC clients
                                    2147483647
                                when d.ss_dtype = 240 then -- Small UDT
                                    isnull(u.max_length, c.max_length)
                                when d.ss_dtype = 241 then -- XML
                                    2147483646
                                when (c.max_length = -1 and d.ss_dtype = 167) then -- varchar(max)
                                    2147483647
                                when (c.max_length = -1 and d.ss_dtype = 231) then -- nvarchar(max)
                                    2147483646
                                when (c.max_length = -1 and d.ss_dtype = 165) then -- varbinary(max)
                                    2147483647
                                else
                                    isnull(d.length, c.max_length)+d.charbin
                                end),
        ORDINAL_POSITION    = convert(int, ColumnProperty(c.object_id, c.name, 'ordinal')),
        IS_NULLABLE         = convert(varchar(254),
                                      rtrim(substring('NO YES',(ColumnProperty (c.object_id, c.name, 'AllowsNull')*3)+1,3))),
        SS_IS_SPARSE        = convert ( smallint, c.is_sparse ),
        SS_IS_COLUMN_SET    = convert ( smallint, c.is_column_set ),
        SS_IS_COMPUTED      = convert(smallint,c.is_computed),
        SS_IS_IDENTITY      = convert(smallint,c.is_identity),
        SS_UDT_CATALOG_NAME = convert(sysname,
                                case
                                when d.ss_dtype = 240 then -- CLR UDTs
                                    db_name()
                                else
                                    null
                                end),
        SS_UDT_SCHEMA_NAME  = convert(sysname,
                                case
                                when d.ss_dtype = 240 then -- CLR UDTs
                                    schema_name(u.schema_id)
                                else
                                    null
                                end),
        SS_UDT_ASSEMBLY_TYPE_NAME    = u.assembly_qualified_name,
        SS_XML_SCHEMACOLLECTION_CATALOG_NAME = convert(sysname,
                                case
                                when (d.ss_dtype = 241 and xsc.name is not null) then -- XML
                                    db_name()
                                else
                                    null
                                end),
        SS_XML_SCHEMACOLLECTION_SCHEMA_NAME  = convert(sysname,
                                case
                                when (d.ss_dtype = 241 and xsc.name is not null) then -- XML
                                    schema_name(xsc.schema_id)
                                else
                                    null
                                end),
        SS_XML_SCHEMACOLLECTION_NAME = xsc.name,
        -- Info here is for backward compatibility - SQL 6.5
        SS_DATA_TYPE        = convert(tinyint,
                                case
                                when d.ss_dtype = 240 then -- CLR UDTs
                                    23
                                else
                                    XTypeToTds(c.system_type_id, c.is_nullable)
                                end),
        -- Used to filter by callers of the view
        OBJECT_TYPE         = o.type

    from
        sys.all_columns c inner join
        sys.all_objects o on
            (
                o.object_id = c.object_id and
                o.type in ('S','U','V', 'TF', 'IF', 'TT')  -- limit columns to tables, views, table-valued functions, and table types only
            ) inner join
        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
            )
        left join
        sys.xml_schema_collections xsc on
            (
                xsc.xml_collection_id = c.xml_collection_id
            )
        left join
        sys.assembly_types u on
            (
                u.user_type_id = c.user_type_id
            )

 
Last revision 2008RTM
See also

  sp_columns (Procedure)
sp_columns_100 (Procedure)
sp_columns_90 (Procedure)
sp_table_type_columns_100 (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