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
