Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.spt_table_type_columns_view

  No additional text.


Syntax

create view sys.spt_table_type_columns_view
as
    select
    -- begin (for doing joins)
        object_id               = o.object_id,
        schema_id               = tt.schema_id,
    -- end (for doing joins)
        TABLE_CATALOG           = db_name(),
    -- TABLE_SCHEMA, and TABLE_NAME columns are defined differently
    -- in this view than in spt_columns_view.
    -- Here they are expose table type metadata rather than table (object) metadata.
    -- These are two different namespaces
        TABLE_SCHEMA            = schema_name(tt.schema_id),
        TABLE_NAME              = tt.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
                                            -- Table type columns aren't securable so we don't need to check
                                            -- for UPDATE permission (permissions(o.object_id,c.name) & 2 = 2)
                                            when c.is_identity != 1
                                                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 --DBTYPE_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 --DBTYPE_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(tt.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,
        -- Table types don't currently support sparse columns or column sets
        
        SS_IS_SPARSE            = convert(bit, 0),
        SS_IS_COLUMN_SET        = convert(bit, 0)

    from
        sys.all_columns c inner join
        sys.all_objects o on
            (
                o.object_id = c.object_id and
                o.type in ('TT') -- limit columns to: table types
            ) inner join
        sys.spt_provider_types d on
            (
                d.ss_dtype = c.system_type_id
            )
        inner join
        sys.table_types tt on
            (
                o.object_id = tt.type_table_object_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(tt.schema_id)) + '.' + quotename(tt.name),
                          'type',
                          'view definition',
                          null,
                          null) = 1)
        -- Table types don't currently support sparse columns or column sets
        
		/*
        and
        (s_cv.SS_IS_SPARSE=0 or objectproperty(s_cv.object_id, 'tablehascolumnset')=0)
        */

 
Last revision 2008RTM
See also

  sp_table_type_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