Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.spt_sparse_columns_view

  No additional text.


Syntax

create view sys.spt_sparse_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),
        IS_NULLABLE             = c.is_nullable,
        DATA_TYPE               = d.oledb_data_type,
        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_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),
        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),
        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_SCHEMA    = convert(sysname,
             case when d.oledb_data_type in (129, 130) --DBTYPE_STR, DBTYPE_WSTR
                                            then N'dbo'
                                            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),
        COLLATION_CATALOG       = convert(sysname,
                                            case when d.oledb_data_type in (129, 130) --DBTYPE_STR, DBTYPE_WSTR
                                            then N'master'
                                            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_NAME          = convert(sysname, c.collation_name),
        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_COMPFLAGS        = convert(int, CollationProperty(c.collation_name, 'oledbcompstyle')),
        COLUMN_SORTID           = nullif(convert(int, DATABASEPROPERTYEX(db_name(),'sqlsortorder')), 0),
        COLUMN_TDSCOLLATION     = convert(binary(5),CollationProperty(c.collation_name, 'TDSCollation')),
        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.all_columns c inner join
        sys.all_objects o on
            (
                o.object_id = c.object_id and
                o.type in ('S','U','V') -- limit columns to: 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
        has_perms_by_name(quotename(schema_name(o.schema_id)) + '.' + quotename(o.name),
                          'object',
                          'select',
                          c.name,
                          'column') = 1

 
Last revision SQL2008SP2
See also

  sp_sparse_columns_100_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