Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.spt_columns_view

 

This is a view in the mssqlsystemresource database. The view spt_columns_view contains all the columns of all databases including all their properties. The view is only usable for the system or an admin connection in single user mode.




Syntax

create view sys.spt_columns_view
as
    select
    -- begin (for doing joins)
        object_id               = o.object_id,
        schema_id               = o.schema_id,
    -- end (for doing joins)
        TABLE_CATALOG           = db_name(),
        TABLE_SCHEMA            = schema_name(o.schema_id),
        TABLE_NAME              = o.name,
        COLUMN_NAME             = c.name,
        COLUMN_GUID             = convert(uniqueidentifier,null),
        COLUMN_PROPID           = convert(int,null),
        ORDINAL_POSITION        = convert(int, ColumnProperty(c.object_id, c.name, 'ordinal')),
        COLUMN_HASDEFAULT       = convert(bit,
                                            case
                                            when ColumnProperty(c.object_id, c.name, 'default') = 0 then 0
                                            else 1
                                            end),
        COLUMN_DEFAULT          = convert(nvarchar(2000),
                                          object_definition(ColumnProperty(c.object_id, c.name, 'default'))
                                         ),
        COLUMN_FLAGS            = convert(int,
                                            case when d.is_long = 1 or c.max_length = -1 -- 2nd condition is for varchar(MAX) types
                                            then 0x82 --DBCOLUMNFLAGS_ISLONG|DBCOLUMNFLAGS_MAYDEFER
                                            else 0
                                            end
                                        |   case when d.fixlen is not null
                                            then 0x10 --DBCOLUMNFLAGS_ISFIXEDLENGTH
                                            else 0
                                            end
                                        |   case when c.is_nullable = 1
                                            then 0x60 --DBCOLUMNFLAGS_ISNULLABLE|DBCOLUMNFLAGS_MAYBENULL
                                            else 0
                                            end
                                        |   case
                                            when d.ss_dtype = 189 -- 'timestamp'
                                                then 0x200 --DBCOLUMNFLAGS_ISROWVER
                                            when c.is_identity != 1 and
                                                (permissions(o.object_id,c.name) & 2 = 2)
                                                then 0x4 --DBCOLUMNFLAGS_WRITE
                                            else 0
                                            end
                                        |   case
                                            when d.ss_dtype in (41, 42, 43) -- datetime2, time, datetimeoffset
                                                then 0x40000000 --DBCOLUMNFLAGS_SS_ISVARIABLESCALE
                                            else 0
                                            end),
        COLUMN_FLAGS_90         = convert(int,
                                            case when d.is_long = 1 or c.max_length = -1 -- 2nd condition is for varchar(MAX) types
                                            then 0x82 --DBCOLUMNFLAGS_ISLONG|DBCOLUMNFLAGS_MAYDEFER
                                            else 0
                                            end
                                        |   case when d.fixlen is not null and d.ss_dtype not in (40, 41, 42, 43)
                                            then 0x10 --DBCOLUMNFLAGS_ISFIXEDLENGTH
                                            else 0
                                            end
                                        |   case when c.is_nullable = 1
                                            then 0x60 --DBCOLUMNFLAGS_ISNULLABLE|DBCOLUMNFLAGS_MAYBENULL
                                            else 0
                                            end
|   case
                                            when d.ss_dtype = 189 -- 'timestamp'
                                                then 0x200 --DBCOLUMNFLAGS_ISROWVER
                                            when c.is_identity != 1 and
                                                (permissions(o.object_id,c.name) & 2 = 2)
                                                then 0x4 --DBCOLUMNFLAGS_WRITE
                                            else 0
                                            end),
        IS_NULLABLE             = c.is_nullable,
        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
                                            when d.oledb_data_type = 132 and c.max_length = -1 then 128 -- Large UDT <--> DBTYPE_BYTES for 9.0 clients
                                            else d.oledb_data_type
                                            end),
        DATA_TYPE_28            = convert(smallint,
                                            case -- Enable 8.0 downlevel clients to see new 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),
        TYPE_GUID               = convert(uniqueidentifier,null),
        CHARACTER_MAXIMUM_LENGTH= convert(int,
                                            case
                                            when d.oledb_data_type in (129, 128) --DBTYPE_STR/DBTYPE_BYTES
                                                then isnull(d.column_size, -- special case text/ntext/image
                                                            (
                                                                case when c.max_length = -1 then 0
                                                                else c.max_length
                                                                end
                                                            ))
                                            when d.oledb_data_type = 130 --DBTYPE_WSTR
                                                then coalesce(d.column_size,c.max_length/2)
                                            when d.oledb_data_type = 132 and c.max_length = -1 -- Large UDT => Unlimited max length
                                                then 0
                                            when d.oledb_data_type = 132 -- Small UDT
                                                then c.max_length
                                            else null
                                            end),
        CHARACTER_MAXIMUM_LENGTH_90= convert(int,
                                            case
                                            when d.oledb_data_type in (129, 128) --DBTYPE_STR/DBTYPE_BYTES
                                                then isnull(d.column_size, -- special case text/ntext/image
                                                            (
                                                                case when c.max_length = -1 then 0
                                                                else c.max_length
                                                                end
                             ))
                                            when d.oledb_data_type = 130 --DBTYPE_WSTR
                                                then coalesce(d.column_size,c.max_length/2)
                                            when d.oledb_data_type = 132 --DBTYPE_UDT
                                                then c.max_length
                                            when d.ss_dtype in (40, 41, 42, 43) -- date, time, datetime2, datetimeoffset
                                                then c.precision
                                            else null
                                            end),
        CHARACTER_MAXIMUM_LENGTH_28= convert(int, -- for backward compatibility
                                            case
                                            when d.oledb_data_type in (129, 128) --DBTYPE_STR/DBTYPE_BYTES
                                                then isnull(d.column_size, -- special case text/image
                                                            (
                                                                case when c.max_length = -1 then 2147483647
                                                                else c.max_length
                                                                end
                                                            ))
                                            when d.oledb_data_type = 130 --DBTYPE_WSTR
                                                then isnull(d.column_size, -- special case ntext
                                                            (
                                                                case when c.max_length = -1 then 1073741823
                                                                else c.max_length/2
                                                                end
                                                            ))
                                            when d.oledb_data_type = 141 --DBTYPE_XML
                                                then 1073741823 -- match ntext value
                                            when d.oledb_data_type = 132 and c.max_length = -1 -- Large UDT => image for non-SNAC clients
                                                then 2147483647
                                            when d.oledb_data_type = 132 -- Small UDT
                                                then c.max_length
                                            when d.ss_dtype in (40, 41, 42, 43) -- date, time, datetime2, datetimeoffset
                                                then c.precision
                                            else null
                                            end),
        CHARACTER_OCTET_LENGTH  = convert(int,
                                            case when d.oledb_data_type in (129, 128) --DBTYPE_STR/DBTYPE_BYTES
                                                then isnull(d.column_size, -- special case text/ntext/image
                                                            (
                                                                case when c.max_length = -1 then 0
                                                                else c.max_length
                                                                end
                                                            ))
                                            when d.oledb_data_type = 130 --DBTYPE_WSTR
                                                then isnull(d.column_size*2, -- special case text/ntext/image
                                                            (
                                                                case when c.max_length = -1 then 0
                                                                else c.max_length
                                                                end
                ))
                                            when d.oledb_data_type = 132 and c.max_length = -1 -- Large UDT => Unlimited Octet Length
                                                then 0
                                            when d.oledb_data_type = 132 -- Small UDT
                                                then c.max_length
                                            else null
                                            end),
        CHARACTER_OCTET_LENGTH_90= convert(int,
                                            case when d.oledb_data_type in (129, 128) --DBTYPE_STR/DBTYPE_BYTES
                                                then isnull(d.column_size, -- special case text/ntext/image
                                                            (
                                                                case when c.max_length = -1 then 0
                                                                else c.max_length
                                                                end
                                                            ))
                                            when d.oledb_data_type = 130 --DBTYPE_WSTR
                                                then isnull(d.column_size*2, -- special case text/ntext/image
                                                            (
                                                                case when c.max_length = -1 then 0
                                                                else c.max_length
                                                                end
                                                            ))
                                            when d.oledb_data_type = 132 --DBTYPE_UDT
                                                then c.max_length
                                            when d.ss_dtype in (40, 41, 42, 43) -- date, time, datetime2, datetimeoffset
                                                then c.precision*2
                                            else null
                                            end),
        CHARACTER_OCTET_LENGTH_28=convert(int, -- for backward compatibility
                                            case
                                            when d.oledb_data_type in (129, 128) --DBTYPE_STR/DBTYPE_BYTES
                                                then isnull(d.column_size, -- special case text/image
                                                            (
                                                                case when c.max_length = -1 then 2147483647
                                                                else c.max_length
                                                                end
                                                            ))
                                            when d.oledb_data_type = 130 --DBTYPE_WSTR
                                                then isnull(d.column_size*2, -- special case ntext
                                                            (
                                                                case when c.max_length = -1 then 2147483646
                                                                else c.max_length
                                                                end
                                                            ))
                                            when d.oledb_data_type = 141 --DBTYPE_XML
                                                then 2147483646 -- match ntext
                                            when d.oledb_data_type = 132 and c.max_length = -1 -- Large UDT => image for non-SNAC clients
                                                then 2147483647
                                            when d.oledb_data_type = 132 --DBTYPE_UDT
                                                then c.max_length
                     when d.ss_dtype in (40, 41, 42, 43) -- date, time, datetime2, datetimeoffset
                                                then c.precision*2
                                            else null
                                            end),
        NUMERIC_PRECISION       = convert(smallint,
                                            case when d.oledb_data_type = 131 --DBTYPE_NUMERIC
                                                then c.precision
                                            when (d.fixed_prec_scale=1 or d.oledb_data_type in (5, 4)) -- DBTYPE_R4/DBTYPE_R8
                                                then d.data_precision
                                            else null
                                            end),
        NUMERIC_SCALE           = convert(smallint,
                                            case when d.oledb_data_type = 131 --DBTYPE_NUMERIC
                                            then odbcscale(c.system_type_id,c.scale)
                                            else null
                                            end),
                                  -- We could also include legacy datetime, smalldatetime in the first condition
                                  -- because the results are correct there also
        DATETIME_PRECISION      = convert(int,
                                            case when d.ss_dtype in (40, 41, 42, 43) -- date, time, datetime2, datetimeoffset
                                            then c.scale
                                            else d.datetime_precision
                                            end),
        DATETIME_PRECISION_90   = convert(int,
                                            case when d.ss_dtype in (40, 41, 42, 43) -- date, time, datetime2, datetimeoffset
                                            then null
                                            else d.datetime_precision
                                            end),
        CHARACTER_SET_CATALOG   = convert(sysname,
                                            case when d.oledb_data_type in (129, 130) --DBTYPE_STR, DBTYPE_WSTR
                                            then N'master'
                                            else null
                                            end),
        CHARACTER_SET_CATALOG_90= convert(sysname, -- for backward compatibility
                                            case when d.oledb_data_type in (129, 130) --DBTYPE_STR/DBTYPE_WSTR
                                            then N'master'
                                            when d.ss_dtype in (40, 41, 42, 43)
                                            then N'master' -- new datetime types characterset catalog belongs to master
                                            else null
                                            end),
        CHARACTER_SET_CATALOG_28= convert(sysname, -- for backward compatibility
                                            case when d.oledb_data_type in (129, 130, 141) --DBTYPE_STR/DBTYPE_WSTR/DBTYPE_XML
                                            then N'master'
                                            when d.ss_dtype in (40, 41, 42, 43)
                                            then N'master' -- new datetime types characterset catalog belongs to master
                                            else null
                                            end),
        CHARACTER_SET_SCHEMA    = convert(sysname,
                                            case when d.oledb_data_type in (129, 130) --DBTYPE_STR, DBTYPE_WSTR
                                            then N'dbo'
                                            else null
                                            end),
        CHARACTER_SET_SCHEMA_90 = convert(sysname, -- for backward compatibility
                                            case when d.oledb_data_type in (129, 130) --DBTYPE_STR/DBTYPE_WSTR/DBTYPE_XML
                                            then N'dbo'
                                            when d.ss_dtype in (40, 41, 42, 43)
                                            then N'dbo' -- new datetime types use the default dbo schema
                                            else null
                                            end),
        CHARACTER_SET_SCHEMA_28 = convert(sysname, -- for backward compatibility
                                            case when d.oledb_data_type in (129, 130, 141) --DBTYPE_STR/DBTYPE_WSTR/DBTYPE_XML
                                            then N'dbo'
                                            when d.ss_dtype in (40, 41, 42, 43)
                                            then N'dbo' -- new datetime types use the default dbo schema
                                            else null
                                            end),
        CHARACTER_SET_NAME      = convert(sysname,
                                            case when d.oledb_data_type in (129, 130) --DBTYPE_STR, DBTYPE_WSTR
                                            then CollationProperty(c.collation_name, 'sqlcharsetname')
                                            else null
                                            end),
        CHARACTER_SET_NAME_90   = convert(sysname, -- for backward compatibility
                                            case
                                            when d.oledb_data_type in (129, 130) --DBTYPE_STR, DBTYPE_WSTR
                                            then CollationProperty(c.collation_name, 'sqlcharsetname')
                                            when d.ss_dtype in (40, 41, 42, 43)
                                            then ServerProperty('sqlcharsetname') -- for new datetime types use the server default charset collation
                                            else null
                                            end),
        CHARACTER_SET_NAME_28   = convert(sysname, -- for backward compatibility
                                            case
                                            when d.oledb_data_type in (129, 130) --DBTYPE_STR, DBTYPE_WSTR
                                            then CollationProperty(c.collation_name, 'sqlcharsetname')
                                            when d.oledb_data_type = 141 --DBTYPE_XML
                                            then ServerProperty('sqlcharsetname')
                                            when d.ss_dtype in (40, 41, 42, 43)
                                            then ServerProperty('sqlcharsetname') -- for new datetime types use the server default charset collation
                                            else null
                                            end),
        COLLATION_CATALOG       = convert(sysname,
                                            case when d.oledb_data_type in (129, 130) --DBTYPE_STR, DBTYPE_WSTR
                                            then N'master'
                                            else null
                                            end),
        COLLATION_CATALOG_90    = convert(sysname, -- for backward compatibility
                                            case when d.oledb_data_type in (129, 130) --DBTYPE_STR/DBTYPE_WSTR/DBTYPE_XML
                                            then N'master'
                                            when d.ss_dtype in (40, 41, 42, 43)
                                            then N'master' -- new datetime types use master as their collation catalog
                                            else null
                                            end),
        COLLATION_CATALOG_28    = convert(sysname, -- for backward compatibility
                                            case when d.oledb_data_type in (129, 130, 141) --DBTYPE_STR/DBTYPE_WSTR/DBTYPE_XML
                            then N'master'
                                            when d.ss_dtype in (40, 41, 42, 43)
                                            then N'master' -- new datetime types use master as their collation catalog
                                            else null
                                            end),
        COLLATION_SCHEMA        = convert(sysname,
                                            case when d.oledb_data_type in (129, 130) --DBTYPE_STR, DBTYPE_WSTR
                                            then N'dbo'
                                            else null
                                            end),
        COLLATION_SCHEMA_90     = convert(sysname, -- for backward compatibility
                                            case when d.oledb_data_type in (129, 130) --DBTYPE_STR/DBTYPE_WSTR/DBTYPE_XML
                                            then N'dbo'
                                            when d.ss_dtype in (40, 41, 42, 43)
                                            then N'dbo' -- new datetime types use the default schema
                                            else null
                                            end),
        COLLATION_SCHEMA_28     = convert(sysname, -- for backward compatibility
                                            case when d.oledb_data_type in (129, 130, 141) --DBTYPE_STR/DBTYPE_WSTR/DBTYPE_XML
                                            then N'dbo'
                                            when d.ss_dtype in (40, 41, 42, 43)
                                            then N'dbo' -- new datetime types use the default schema
                                            else null
                                            end),
        COLLATION_NAME          = convert(sysname, c.collation_name),
        COLLATION_NAME_90       = convert(sysname, -- for backward compatibility
                                            case
                                            when d.oledb_data_type in (129, 130) --DBTYPE_STR/DBTYPE_WSTR
                                                then c.collation_name
                                            when d.ss_dtype in (40, 41, 42, 43)
                                                then ServerProperty('collation') -- new datetime types have no column collation
                                            else null
                                            end),
        COLLATION_NAME_28       = convert(sysname, -- for backward compatibility
                                            case
                                            when d.oledb_data_type in (129, 130) --DBTYPE_STR/DBTYPE_WSTR
                                                then c.collation_name
                                            when d.oledb_data_type = 141 --DBTYPE_XML
                                                then ServerProperty('collation') -- no column collation for xml, so use server default
                                            when d.ss_dtype in (40, 41, 42, 43)
                                                then ServerProperty('collation') -- new datetime types have no column collation
                                            else null
                                            end),
        DOMAIN_CATALOG          = case when c.user_type_id < 256 -- regular types, without SQL UDTs
                                  then null
                                  else db_name()
                                  end,
        DOMAIN_SCHEMA           = case when c.user_type_id < 256 -- regular types, without SQL UDTs
                                  then null
                                  else schema_name(o.schema_id)
                                  end,
        DOMAIN_NAME             = case when c.user_type_id < 256 -- regular types, without SQL UDTs
                                  then null
      else type_name(c.user_type_id)
                                  end,
        DESCRIPTION             = convert(nvarchar(1),null),
        COLUMN_LCID             = convert(int, CollationProperty(c.collation_name, 'lcid')),
        COLUMN_LCID_90          = convert(int, -- for backward compatibility
                                            case
                                            when d.oledb_data_type in (129, 130) --DBTYPE_STR/DBTYPE_WSTR
                                                then CollationProperty(c.collation_name, 'lcid')
                                            when d.ss_dtype in (40, 41, 42, 43)
                                                then ServerProperty('lcid') -- new datetime types use the server default lcid
                                            else null
                                            end),
        COLUMN_LCID_28          = convert(int, -- for backward compatibility
                                            case
                                            when d.oledb_data_type in (129, 130) --DBTYPE_STR/DBTYPE_WSTR
                                                then CollationProperty(c.collation_name, 'lcid')
                                            when d.oledb_data_type = 141 --DBTYPE_XML
                                                then ServerProperty('lcid') -- no column collation for xml, so use server default
                                            when d.ss_dtype in (40, 41, 42, 43)
                                                then ServerProperty('lcid') -- new datetime types use the server default lcid
                                            else null
                                            end),
        COLUMN_COMPFLAGS        = convert(int, CollationProperty(c.collation_name, 'oledbcompstyle')),
        COLUMN_COMPFLAGS_90     = convert(int, -- for backward compatibility
                                            case
                                            when d.oledb_data_type in (129, 130) --DBTYPE_STR/DBTYPE_WSTR
                                                then CollationProperty(c.collation_name, 'oledbcompstyle')
                                            when d.ss_dtype in (40, 41, 42, 43)
                                                then CollationProperty(convert(sysname, ServerProperty('collation')),'oledbcompstyle')
                                            else null
                                            end),
        COLUMN_COMPFLAGS_28     = convert(int, -- for backward compatibility
                                            case
                                            when d.oledb_data_type in (129, 130) --DBTYPE_STR/DBTYPE_WSTR
                                                then CollationProperty(c.collation_name, 'oledbcompstyle')
                                            when d.oledb_data_type = 141 --DBTYPE_XML
                                                -- no column collation for xml, so use server default
                                                then CollationProperty(convert(sysname, ServerProperty('collation')),'oledbcompstyle')
                                            when d.ss_dtype in (40, 41, 42, 43)
                                                then CollationProperty(convert(sysname, ServerProperty('collation')),'oledbcompstyle')
                                            else null
                                            end),
        COLUMN_SORTID           = nullif(convert(int, DATABASEPROPERTYEX(db_name(),'sqlsortorder')), 0),
        COLUMN_TDSCOLLATION     = convert(binary(5),CollationProperty(c.collation_name, 'TDSCollation')),
        COLUMN_TDSCOLLATION_90  = convert(binary(5), -- for backward compatibility
                                            case
                                            when d.oledb_data_type in (129, 130) --DBTYPE_STR/DBTYPE_WSTR
             then CollationProperty(c.collation_name, 'TDSCollation')
                                            when d.ss_dtype in (40, 41, 42, 43)
                                                then CollationProperty(convert(sysname, ServerProperty('collation')),'TDSCollation')
                                            else null
                                            end),
        COLUMN_TDSCOLLATION_28  = convert(binary(5), -- for backward compatibility
                                            case
                                            when d.oledb_data_type in (129, 130) --DBTYPE_STR/DBTYPE_WSTR
                                                then CollationProperty(c.collation_name, 'TDSCollation')
                                            when d.oledb_data_type = 141 --DBTYPE_XML
                                                -- no column collation for xml, so use server default
                                                then CollationProperty(convert(sysname, ServerProperty('collation')),'TDSCollation')
                                            when d.ss_dtype in (40, 41, 42, 43)
                                                then CollationProperty(convert(sysname, ServerProperty('collation')),'TDSCollation')
                                            else null
                                            end),
        IS_COMPUTED             = convert(bit, ColumnProperty(c.object_id, c.name, 'IsComputed')),
        SS_XML_SCHEMACOLLECTION_CATALOGNAME = convert(sysname,
                                              case when (d.ss_dtype = 241 and xsc.name is not null)
                                              then db_name()
                                              else null
                                              end),
        SS_XML_SCHEMACOLLECTION_SCHEMANAME  = convert(sysname,
                                              case when (d.ss_dtype = 241 and xsc.name is not null)
                                              then schema_name(xsc.schema_id)
                                              else null
                                              end),
        SS_XML_SCHEMACOLLECTIONNAME         = convert(sysname, xsc.name),
        SS_UDT_CATALOGNAME      = convert(sysname,
                                            case when d.ss_dtype = 240 -- CLR UDT
                                            then db_name()
                                            else null
                                            end),
        SS_UDT_SCHEMANAME       = convert(sysname, schema_name(u.schema_id)),
        SS_UDT_NAME             = convert(sysname, u.name),
        SS_UDT_ASSEMBLY_TYPENAME= u.assembly_qualified_name,
        SS_IS_SPARSE            = c.is_sparse,
        SS_IS_COLUMN_SET        = c.is_column_set

    from
        sys.columns c inner join
        sys.objects o on
            (
                o.object_id = c.object_id and
                o.type in ('U','V') -- user tables, views
            ) inner join
        sys.spt_provider_types d on
            (
                d.ss_dtype = c.system_type_id
            )
        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
            )

     where
		(c.is_sparse = 0 or objectproperty(o.object_id, 'tablehascolumnset') = 0) and
        has_perms_by_name(quotename(schema_name(o.schema_id)) + '.' + quotename(o.name),
                          'object',
                          'select',
                          c.name,
                          'column') = 1

	UNION ALL

    select
    -- begin (for doing joins)
        object_id               = o.object_id,
        schema_id               = o.schema_id,
    -- end (for doing joins)
TABLE_CATALOG           = db_name(),
        TABLE_SCHEMA            = schema_name(o.schema_id),
        TABLE_NAME              = o.name,
        COLUMN_NAME             = c.name,
        COLUMN_GUID             = convert(uniqueidentifier,null),
        COLUMN_PROPID           = convert(int,null),
        ORDINAL_POSITION        = convert(int, ColumnProperty(c.object_id, c.name, 'ordinal')),
        COLUMN_HASDEFAULT       = convert(bit,
                                            case
                                            when ColumnProperty(c.object_id, c.name, 'default') = 0 then 0
                                            else 1
                                            end),
        COLUMN_DEFAULT          = convert(nvarchar(2000),
                                          object_definition(ColumnProperty(c.object_id, c.name, 'default'))
                                         ),
        COLUMN_FLAGS            = convert(int,
                                            case when d.is_long = 1 or c.max_length = -1 -- 2nd condition is for varchar(MAX) types
                                            then 0x82 --DBCOLUMNFLAGS_ISLONG|DBCOLUMNFLAGS_MAYDEFER
                                            else 0
                                            end
                                        |   case when d.fixlen is not null
                                            then 0x10 --DBCOLUMNFLAGS_ISFIXEDLENGTH
                                            else 0
                                            end
                                        |   case when c.is_nullable = 1
                                            then 0x60 --DBCOLUMNFLAGS_ISNULLABLE|DBCOLUMNFLAGS_MAYBENULL
                                            else 0
                                            end
                                        |   case
                                            when d.ss_dtype = 189 -- 'timestamp'
                                                then 0x200 --DBCOLUMNFLAGS_ISROWVER
                                            when c.is_identity != 1 and
                                                (permissions(o.object_id,c.name) & 2 = 2)
                                                then 0x4 --DBCOLUMNFLAGS_WRITE
                                            else 0
                                            end
                                        |   case
                                            when d.ss_dtype in (41, 42, 43) -- datetime2, time, datetimeoffset
                                                then 0x40000000 --DBCOLUMNFLAGS_SS_ISVARIABLESCALE
                                            else 0
                                            end),
        COLUMN_FLAGS_90         = convert(int,
                                            case when d.is_long = 1 or c.max_length = -1 -- 2nd condition is for varchar(MAX) types
                                            then 0x82 --DBCOLUMNFLAGS_ISLONG|DBCOLUMNFLAGS_MAYDEFER
                                            else 0
                                            end
                                        |   case when d.fixlen is not null and d.ss_dtype not in (40, 41, 42, 43)
                                            then 0x10 --DBCOLUMNFLAGS_ISFIXEDLENGTH
                                            else 0
                                            end
                                        |   case when c.is_nullable = 1
                                            then 0x60 --DBCOLUMNFLAGS_ISNULLABLE|DBCOLUMNFLAGS_MAYBENULL
                                            else 0
                                            end
                                        |   case
                                            when d.ss_dtype = 189 -- 'timestamp'
                                                then 0x200 --DBCOLUMNFLAGS_ISROWVER
   when c.is_identity != 1 and
                                                (permissions(o.object_id,c.name) & 2 = 2)
                                                then 0x4 --DBCOLUMNFLAGS_WRITE
                                            else 0
                                            end),
        IS_NULLABLE             = c.is_nullable,
        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
                                            when d.oledb_data_type = 132 and c.max_length = -1 then 128 -- Large UDT <--> DBTYPE_BYTES for 9.0 clients
                                            else d.oledb_data_type
                                            end),
        DATA_TYPE_28            = convert(smallint,
                                            case -- Enable 8.0 downlevel clients to see new 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),
        TYPE_GUID               = convert(uniqueidentifier,null),
        CHARACTER_MAXIMUM_LENGTH= convert(int,
                                            case
                                            when d.oledb_data_type in (129, 128) --DBTYPE_STR/DBTYPE_BYTES
                                                then isnull(d.column_size, -- special case text/ntext/image
                                                            (
                                                                case when c.max_length = -1 then 0
                                                                else c.max_length
                                                                end
                                                            ))
                                            when d.oledb_data_type = 130 --DBTYPE_WSTR
                                                then coalesce(d.column_size,c.max_length/2)
                                            when d.oledb_data_type = 132 and c.max_length = -1 -- Large UDT => Unlimited max length
                                                then 0
                                            when d.oledb_data_type = 132 -- Small UDT
                                                then c.max_length
                                            else null
                                            end),
        CHARACTER_MAXIMUM_LENGTH_90= convert(int,
                                            case
                                            when d.oledb_data_type in (129, 128) --DBTYPE_STR/DBTYPE_BYTES
                                                then isnull(d.column_size, -- special case text/ntext/image
                                                            (
                                                                case when c.max_length = -1 then 0
                                                                else c.max_length
                                                                end
                                                            ))
                                            when d.oledb_data_type = 130 --DBTYPE_WSTR
                                                then coalesce(d.column_size,c.max_length/2)
                                        when d.oledb_data_type = 132 --DBTYPE_UDT
                                                then c.max_length
                                            when d.ss_dtype in (40, 41, 42, 43) -- date, time, datetime2, datetimeoffset
                                                then c.precision
                                            else null
                                            end),
        CHARACTER_MAXIMUM_LENGTH_28= convert(int, -- for backward compatibility
                                            case
                                            when d.oledb_data_type in (129, 128) --DBTYPE_STR/DBTYPE_BYTES
                                                then isnull(d.column_size, -- special case text/image
                                                            (
                                                                case when c.max_length = -1 then 2147483647
                                                                else c.max_length
                                                                end
                                                            ))
                                            when d.oledb_data_type = 130 --DBTYPE_WSTR
                                                then isnull(d.column_size, -- special case ntext
                                                            (
                                                                case when c.max_length = -1 then 1073741823
                                                                else c.max_length/2
                                                                end
                                                            ))
                                            when d.oledb_data_type = 141 --DBTYPE_XML
                                                then 1073741823 -- match ntext value
                                            when d.oledb_data_type = 132 and c.max_length = -1 -- Large UDT => image for non-SNAC clients
                                                then 2147483647
                                            when d.oledb_data_type = 132 -- Small UDT
                                                then c.max_length
                                            when d.ss_dtype in (40, 41, 42, 43) -- date, time, datetime2, datetimeoffset
                                                then c.precision
                                            else null
                                            end),
        CHARACTER_OCTET_LENGTH  = convert(int,
                                            case when d.oledb_data_type in (129, 128) --DBTYPE_STR/DBTYPE_BYTES
                                                then isnull(d.column_size, -- special case text/ntext/image
                                                            (
                                                                case when c.max_length = -1 then 0
                                                                else c.max_length
                                                                end
                                                            ))
                                            when d.oledb_data_type = 130 --DBTYPE_WSTR
                                                then isnull(d.column_size*2, -- special case text/ntext/image
                                                            (
                                                                case when c.max_length = -1 then 0
                                                                else c.max_length
                                                                end
                                                            ))
                                            when d.oledb_data_type = 132 and c.max_length = -1 -- Large UDT => Unlimited Octet Length
                                                then 0
                                     when d.oledb_data_type = 132 -- Small UDT
                                                then c.max_length
                                            else null
                                            end),
        CHARACTER_OCTET_LENGTH_90= convert(int,
                                            case when d.oledb_data_type in (129, 128) --DBTYPE_STR/DBTYPE_BYTES
                                                then isnull(d.column_size, -- special case text/ntext/image
                                                            (
                                                                case when c.max_length = -1 then 0
                                                                else c.max_length
                                                                end
                                                            ))
                                            when d.oledb_data_type = 130 --DBTYPE_WSTR
                                                then isnull(d.column_size*2, -- special case text/ntext/image
                                                            (
                                                                case when c.max_length = -1 then 0
                                                                else c.max_length
                                                                end
                                                            ))
                                            when d.oledb_data_type = 132 --DBTYPE_UDT
                                                then c.max_length
                                            when d.ss_dtype in (40, 41, 42, 43) -- date, time, datetime2, datetimeoffset
                                                then c.precision*2
                                            else null
                                            end),
        CHARACTER_OCTET_LENGTH_28=convert(int, -- for backward compatibility
                                            case
                                            when d.oledb_data_type in (129, 128) --DBTYPE_STR/DBTYPE_BYTES
                                                then isnull(d.column_size, -- special case text/image
                                                            (
                                                                case when c.max_length = -1 then 2147483647
                                                                else c.max_length
                                                                end
                                                            ))
                                            when d.oledb_data_type = 130 --DBTYPE_WSTR
                                                then isnull(d.column_size*2, -- special case ntext
                                                            (
                                                                case when c.max_length = -1 then 2147483646
                                                                else c.max_length
                                                                end
                                                            ))
                                            when d.oledb_data_type = 141 --DBTYPE_XML
                                                then 2147483646 -- match ntext
                                            when d.oledb_data_type = 132 and c.max_length = -1 -- Large UDT => image for non-SNAC clients
                                                then 2147483647
                                            when d.oledb_data_type = 132 --DBTYPE_UDT
                                                then c.max_length
                                            when d.ss_dtype in (40, 41, 42, 43) -- date, time, datetime2, datetimeoffset
                                                then c.precision*2
                                            else null
                                            end),
        NUMERIC_PRECISION       = convert(smallint,
                                            case when d.oledb_data_type = 131 --DBTYPE_NUMERIC
                                                then c.precision
                                            when (d.fixed_prec_scale=1 or d.oledb_data_type in (5, 4)) -- DBTYPE_R4/DBTYPE_R8
                                                then d.data_precision
                                            else null
                                            end),
        NUMERIC_SCALE           = convert(smallint,
                                            case when d.oledb_data_type = 131 --DBTYPE_NUMERIC
                                            then odbcscale(c.system_type_id,c.scale)
                                            else null
                                            end),
                                  -- We could also include legacy datetime, smalldatetime in the first condition
                                  -- because the results are correct there also
        DATETIME_PRECISION      = convert(int,
                                            case when d.ss_dtype in (40, 41, 42, 43) -- date, time, datetime2, datetimeoffset
                                            then c.scale
                                            else d.datetime_precision
                                            end),
        DATETIME_PRECISION_90   = convert(int,
                                            case when d.ss_dtype in (40, 41, 42, 43) -- date, time, datetime2, datetimeoffset
                                            then null
                                            else d.datetime_precision
                                            end),
        CHARACTER_SET_CATALOG   = convert(sysname,
                                            case when d.oledb_data_type in (129, 130) --DBTYPE_STR, DBTYPE_WSTR
                                            then N'master'
                                            else null
                                            end),
        CHARACTER_SET_CATALOG_90= convert(sysname, -- for backward compatibility
                                            case when d.oledb_data_type in (129, 130) --DBTYPE_STR/DBTYPE_WSTR
                                            then N'master'
                                            when d.ss_dtype in (40, 41, 42, 43)
                                            then N'master' -- new datetime types characterset catalog belongs to master
                                            else null
                                            end),
        CHARACTER_SET_CATALOG_28= convert(sysname, -- for backward compatibility
                                            case when d.oledb_data_type in (129, 130, 141) --DBTYPE_STR/DBTYPE_WSTR/DBTYPE_XML
                                            then N'master'
                                            when d.ss_dtype in (40, 41, 42, 43)
                                            then N'master' -- new datetime types characterset catalog belongs to master
                                            else null
                                            end),
        CHARACTER_SET_SCHEMA    = convert(sysname,
                                            case when d.oledb_data_type in (129, 130) --DBTYPE_STR, DBTYPE_WSTR
                                            then N'dbo'
                                            else null
                                            end),
        CHARACTER_SET_SCHEMA_90 = convert(sysname, -- for backward compatibility
                                            case when d.oledb_data_type in (129, 130) --DBTYPE_STR/DBTYPE_WSTR/DBTYPE_XML
                                            then N'dbo'
                                            when d.ss_dtype in (40, 41, 42, 43)
           then N'dbo' -- new datetime types use the default dbo schema
                                            else null
                                            end),
        CHARACTER_SET_SCHEMA_28 = convert(sysname, -- for backward compatibility
                                            case when d.oledb_data_type in (129, 130, 141) --DBTYPE_STR/DBTYPE_WSTR/DBTYPE_XML
                                            then N'dbo'
                                            when d.ss_dtype in (40, 41, 42, 43)
                                            then N'dbo' -- new datetime types use the default dbo schema
                                            else null
                                            end),
        CHARACTER_SET_NAME      = convert(sysname,
                                            case when d.oledb_data_type in (129, 130) --DBTYPE_STR, DBTYPE_WSTR
                                            then CollationProperty(c.collation_name, 'sqlcharsetname')
                                            else null
                                            end),
        CHARACTER_SET_NAME_90   = convert(sysname, -- for backward compatibility
                                            case
                                            when d.oledb_data_type in (129, 130) --DBTYPE_STR, DBTYPE_WSTR
                                            then CollationProperty(c.collation_name, 'sqlcharsetname')
                                            when d.ss_dtype in (40, 41, 42, 43)
                                            then ServerProperty('sqlcharsetname') -- for new datetime types use the server default charset collation
                                            else null
                                            end),
        CHARACTER_SET_NAME_28   = convert(sysname, -- for backward compatibility
                                            case
                                            when d.oledb_data_type in (129, 130) --DBTYPE_STR, DBTYPE_WSTR
                                            then CollationProperty(c.collation_name, 'sqlcharsetname')
                                            when d.oledb_data_type = 141 --DBTYPE_XML
                                            then ServerProperty('sqlcharsetname')
                                            when d.ss_dtype in (40, 41, 42, 43)
                                            then ServerProperty('sqlcharsetname') -- for new datetime types use the server default charset collation
                                            else null
                                            end),
        COLLATION_CATALOG       = convert(sysname,
                                            case when d.oledb_data_type in (129, 130) --DBTYPE_STR, DBTYPE_WSTR
                                            then N'master'
                                            else null
                                            end),
        COLLATION_CATALOG_90    = convert(sysname, -- for backward compatibility
                                            case when d.oledb_data_type in (129, 130) --DBTYPE_STR/DBTYPE_WSTR/DBTYPE_XML
                                            then N'master'
                                            when d.ss_dtype in (40, 41, 42, 43)
                                            then N'master' -- new datetime types use master as their collation catalog
                                            else null
                                            end),
        COLLATION_CATALOG_28    = convert(sysname, -- for backward compatibility
                                            case when d.oledb_data_type in (129, 130, 141) --DBTYPE_STR/DBTYPE_WSTR/DBTYPE_XML
                                            then N'master'
                                            when d.ss_dtype in (40, 41, 42, 43)
                                            then N'master' -- new datetime types use master as their collation catalog
                                            else null
                                            end),
        COLLATION_SCHEMA        = convert(sysname,
                                            case when d.oledb_data_type in (129, 130) --DBTYPE_STR, DBTYPE_WSTR
                                            then N'dbo'
                                            else null
                                            end),
        COLLATION_SCHEMA_90     = convert(sysname, -- for backward compatibility
                                            case when d.oledb_data_type in (129, 130) --DBTYPE_STR/DBTYPE_WSTR/DBTYPE_XML
                                            then N'dbo'
                                            when d.ss_dtype in (40, 41, 42, 43)
                                            then N'dbo' -- new datetime types use the default schema
                                            else null
                                            end),
        COLLATION_SCHEMA_28     = convert(sysname, -- for backward compatibility
                                            case when d.oledb_data_type in (129, 130, 141) --DBTYPE_STR/DBTYPE_WSTR/DBTYPE_XML
                                            then N'dbo'
                                            when d.ss_dtype in (40, 41, 42, 43)
                                            then N'dbo' -- new datetime types use the default schema
                                            else null
                                            end),
        COLLATION_NAME          = convert(sysname, c.collation_name),
        COLLATION_NAME_90       = convert(sysname, -- for backward compatibility
                                            case
                                            when d.oledb_data_type in (129, 130) --DBTYPE_STR/DBTYPE_WSTR
                                                then c.collation_name
                                            when d.ss_dtype in (40, 41, 42, 43)
                                                then ServerProperty('collation') -- new datetime types have no column collation
                                            else null
                                            end),
        COLLATION_NAME_28       = convert(sysname, -- for backward compatibility
                                            case
                                            when d.oledb_data_type in (129, 130) --DBTYPE_STR/DBTYPE_WSTR
                                                then c.collation_name
                                            when d.oledb_data_type = 141 --DBTYPE_XML
                                                then ServerProperty('collation') -- no column collation for xml, so use server default
                                            when d.ss_dtype in (40, 41, 42, 43)
                                                then ServerProperty('collation') -- new datetime types have no column collation
                                            else null
                                            end),
        DOMAIN_CATALOG          = case when c.user_type_id < 256 -- regular types, without SQL UDTs
                                  then null
                                  else db_name()
                                  end,
        DOMAIN_SCHEMA           = case when c.user_type_id < 256 -- regular types, without SQL UDTs
                                  then null
                                  else schema_name(o.schema_id)
                                  end,
        DOMAIN_NAME             = case when c.user_type_id < 256 -- regular types, without SQL UDTs
                                  then null
                                  else type_name(c.user_type_id)
                                  end,
        DESCRIPTION             = convert(nvarchar(1),null),
        COLUMN_LCID             = convert(int, CollationProperty(c.collation_name, 'lcid')),
        COLUMN_LCID_90          = convert(int, -- for backward compatibility
                                            case
                                            when d.oledb_data_type in (129, 130) --DBTYPE_STR/DBTYPE_WSTR
                                                then CollationProperty(c.collation_name, 'lcid')
                                            when d.ss_dtype in (40, 41, 42, 43)
                                                then ServerProperty('lcid') -- new datetime types use the server default lcid
                                            else null
                                            end),
        COLUMN_LCID_28          = convert(int, -- for backward compatibility
                                            case
                                            when d.oledb_data_type in (129, 130) --DBTYPE_STR/DBTYPE_WSTR
                                                then CollationProperty(c.collation_name, 'lcid')
                                            when d.oledb_data_type = 141 --DBTYPE_XML
                                                then ServerProperty('lcid') -- no column collation for xml, so use server default
                                            when d.ss_dtype in (40, 41, 42, 43)
                                                then ServerProperty('lcid') -- new datetime types use the server default lcid
                                            else null
                                            end),
        COLUMN_COMPFLAGS        = convert(int, CollationProperty(c.collation_name, 'oledbcompstyle')),
        COLUMN_COMPFLAGS_90     = convert(int, -- for backward compatibility
                                            case
                                            when d.oledb_data_type in (129, 130) --DBTYPE_STR/DBTYPE_WSTR
                               
 
Last revision SQL2008SP2
See also

  spt_columns_view_managed (View)
spt_table_type_columns_view (View)
sp_columns_100_rowset (Procedure)
sp_columns_100_rowset2 (Procedure)
sp_columns_90_rowset (Procedure)
sp_columns_90_rowset2 (Procedure)
sp_columns_managed (Procedure)
sp_columns_rowset (Procedure)
sp_columns_rowset2 (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