April 17, 2012

sp_columns_ex_90 (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_columns_ex_90(nvarchar @table_server
, nvarchar @table_name
, nvarchar @table_schema
, nvarchar @table_catalog
, nvarchar @column_name
, int @ODBCVer
, bit @fUsePattern)

MetaData:

   
create procedure sys.sp_columns_ex_90
(
@table_server sysname,
@table_name sysname = null,
@table_schema sysname = null,
@table_catalog sysname = null,
@column_name sysname = null,
@ODBCVer int = 2,
@fUsePattern bit = 1 -- To allow users to explicitly disable all pattern matching.
)
as
set nocount on
declare
@DBCOLUMNFLAGS_ISFIXEDLENGTH binary(1), @DBCOLUMNFLAGS_ISLONG binary(1),
@DBCOLUMNFLAGS_ISROWVER binary(2), @DBCOLUMNFLAGS_SS_ISVARIABLESCALE binary(4)

select
@DBCOLUMNFLAGS_ISFIXEDLENGTH = 0x10, @DBCOLUMNFLAGS_ISLONG = 0x80,
@DBCOLUMNFLAGS_ISROWVER = 0x0200, @DBCOLUMNFLAGS_SS_ISVARIABLESCALE = 0x40000000

declare
@DBTYPE_I2 smallint, @DBTYPE_I4 smallint,
@DBTYPE_R4 smallint, @DBTYPE_R8 smallint,
@DBTYPE_CY smallint, @DBTYPE_DATE smallint,
@DBTYPE_BSTR smallint, @DBTYPE_BOOL smallint,
@DBTYPE_VARNUMERIC smallint, @DBTYPE_DECIMAL smallint,
@DBTYPE_UI1 smallint, @DBTYPE_BYREF smallint,
@DBTYPE_I1 smallint, @DBTYPE_UI2 smallint,
@DBTYPE_UI4 smallint, @DBTYPE_I8 smallint,
@DBTYPE_UI8 smallint, @DBTYPE_GUID smallint,
@DBTYPE_BYTES smallint, @DBTYPE_STR smallint,
@DBTYPE_WSTR smallint, @DBTYPE_NUMERIC smallint,
@DBTYPE_DBDATE smallint, @DBTYPE_DBTIME smallint,
@DBTYPE_DBTIMESTAMP smallint, @DBTYPE_XML smallint,
@DBTYPE_DBTIME2 smallint, @DBTYPE_DBTIMESTAMPOFFSET smallint

if (@ODBCVer is null) or (@ODBCVer <> 3)
select @ODBCVer = 2

select
@DBTYPE_I2 = 2, @DBTYPE_I4 = 3,
@DBTYPE_R4 = 4, @DBTYPE_R8 = 5,
@DBTYPE_CY = 6, @DBTYPE_DATE = 7,
@DBTYPE_BSTR = 8, @DBTYPE_BOOL = 11,
@DBTYPE_VARNUMERIC = 139, @DBTYPE_DECIMAL = 14,
@DBTYPE_UI1 = 17, @DBTYPE_BYREF = 16384,
@DBTYPE_I1 = 16, @DBTYPE_UI2 = 18,
@DBTYPE_UI4 = 19, @DBTYPE_I8 = 20,
@DBTYPE_UI8 = 21, @DBTYPE_GUID = 72,
@DBTYPE_BYTES = 128, @DBTYPE_STR = 129,
@DBTYPE_WSTR = 130, @DBTYPE_NUMERIC = 131,
@DBTYPE_DBDATE = 133, @DBTYPE_DBTIME = 134,
@DBTYPE_DBTIMESTAMP = 135, @DBTYPE_XML = 141,
@DBTYPE_DBTIME2 = 145, @DBTYPE_DBTIMESTAMPOFFSET = 146

declare
@ODS_SMALLINT_NOT_NULL tinyint, @ODS_INT_NULL tinyint,
@ODS_INT_NOT_NULL tinyint, @ODS_TINYINT_NOT_NULL tinyint,
@ODS_REAL_NOT_NULL tinyint, @ODS_FLOAT_NULL tinyint,
@ODS_FLOAT_NOT_NULL tinyint, @ODS_MONEY_NULL tinyint,
@ODS_MONEY_NOT_NULL tinyint, @ODS_DATETIME_NULL tinyint,
@ODS_DATETIME_NOT_NULL tinyint, @ODS_TEXT tinyint,
@ODS_BIT tinyint, @ODS_DECIMAL tinyint,
@ODS_NUMERIC tinyint, @ODS_GUID tinyint,
@ODS_IMAGE tinyint, @ODS_TIMESTAMP tinyint,
@ODS_BINARY_NULL tinyint, @ODS_BINARY_NOT_NULL tinyint,
@ODS_VARBINARY tinyint, @ODS_CHAR_NULL tinyint,
@ODS_CHAR_NOT_NULL tinyint, @ODS_VARCHAR tinyint,
@ODS_DATE tinyint, @ODS_TIME tinyint,
@ODS_DATETIME2 tinyint, @ODS_DATETIMEOFFSET tinyint

select
@ODS_IMAGE = 34, @ODS_TEXT = 35,
@ODS_GUID = 36, @ODS_BINARY_NULL = 37,
@ODS_VARBINARY = 37, @ODS_INT_NULL = 38,
@ODS_CHAR_NULL = 39, @ODS_VARCHAR = 39,
@ODS_TIMESTAMP = 45, @ODS_BINARY_NOT_NULL = 45,
@ODS_CHAR_NOT_NULL = 47, @ODS_TINYINT_NOT_NULL = 48,
@ODS_BIT = 50, @ODS_SMALLINT_NOT_NULL = 52,
@ODS_INT_NOT_NULL = 56, @ODS_REAL_NOT_NULL = 59,
@ODS_MONEY_NOT_NULL = 60, @ODS_DATETIME_NOT_NULL = 61,
@ODS_FLOAT_NOT_NULL = 62, @ODS_DECIMAL = 106,
@ODS_NUMERIC = 108, @ODS_FLOAT_NULL = 109,
@ODS_MONEY_NULL = 110, @ODS_DATETIME_NULL = 111,
@ODS_DATE = 40, @ODS_TIME = 41,
@ODS_DATETIME2 = 42, @ODS_DATETIMEOFFSET = 43

declare
@ODBC_GUID smallint, @ODBC_NTEXT smallint,
@ODBC_NVARCHAR smallint, @ODBC_NCHAR smallint,
@ODBC_BIT smallint, @ODBC_TINYINT smallint,
@ODBC_IMAGE smallint, @ODBC_VARBINARY smallint,
@ODBC_TIMESTAMP smallint, @ODBC_BINARY smallint,
@ODBC_TEXT smallint, @ODBC_CHAR smallint,
@ODBC_NUMERIC smallint, @ODBC_DECIMAL smallint,
@ODBC_MONEY smallint, @ODBC_INT smallint,
@ODBC_SMALLINT smallint, @ODBC_FLOAT smallint,
@ODBC_REAL smallint, @ODBC_VARCHAR smallint,
@ODBC_DATETIME smallint, @ODBC_DATE smallint,
@ODBC_TIME2 smallint, @ODBC_DATETIMEOFFSET smallint

select
@ODBC_GUID = -11, @ODBC_NTEXT = -10,
@ODBC_NVARCHAR = -9, @ODBC_NCHAR = -8,
@ODBC_BIT = -7, @ODBC_TINYINT = -6,
@ODBC_IMAGE = -4, @ODBC_VARBINARY = -3,
@ODBC_TIMESTAMP = -2, @ODBC_BINARY = -2,
@ODBC_TEXT = -1, @ODBC_CHAR = 1,
@ODBC_NUMERIC = 2, @ODBC_DECIMAL = 3,
@ODBC_MONEY = 3, @ODBC_INT = 4,
@ODBC_SMALLINT = 5, @ODBC_FLOAT = 6,
@ODBC_REAL = 7, @ODBC_VARCHAR = 12,
@ODBC_DATETIME = case @ODBCVer
when 2 then 11
else 93
end,
@ODBC_DATE = case @ODBCVer
when 2 then 9
else 91
end,
@ODBC_TIME2 = -154, @ODBC_DATETIMEOFFSET = -155

create table #tmp_columns
(
TABLE_CAT sysname collate catalog_default NULL,
TABLE_SCHEM sysname collate catalog_default NULL,
TABLE_NAME sysname collate catalog_default NOT NULL,
COLUMN_NAME sysname collate catalog_default NULL,
DATA_TYPE smallint NOT NULL,
TYPE_NAME sysname collate catalog_default NULL,
COLUMN_SIZE int NULL,
BUFFER_LENGTH int NULL,
DECIMAL_DIGITS smallint NULL,
NUM_PREC_RADIX smallint NULL,
NULLABLE smallint NOT NULL,
REMARKS nvarchar(254) collate catalog_default NULL,
COLUMN_DEF nvarchar(254) collate catalog_default NULL,
SQL_DATA_TYPE smallint null,
SQL_DATETIME_SUB smallint NULL,
CHAR_OCTET_LENGTH int NULL,
ORDINAL_POSITION smallint,
IS_NULLABLE varchar(254) collate catalog_default NOT NULL,
SS_UDT_CATALOG_NAME sysname collate catalog_default NULL,
SS_UDT_SCHEMA_NAME sysname collate catalog_default NULL,
SS_UDT_ASSEMBLY_TYPE_NAME nvarchar(4000) collate Latin1_General_BIN NULL,
SS_XML_SCHEMACOLLECTION_CATALOG_NAME sysname collate catalog_default NULL,
SS_XML_SCHEMACOLLECTION_SCHEMA_NAME sysname collate catalog_default NULL,
SS_XML_SCHEMACOLLECTION_NAME sysname collate catalog_default NULL,
SS_DATA_TYPE tinyint null,-- Info here is for backward compatibility - SQL 6.5
COLUMN_FLAGS int NOT NULL -- This is not returned in the final resultset, but used in conditional logic
)

--
-- Gathers column info through DQ(OLEDB) from remote server.
--
if (@fUsePattern = 1) -- Does the user want it?
begin
if ((isnull(charindex('%', @table_name),0) = 0) and
(isnull(charindex('_', @table_name),0) = 0) and
(isnull(charindex('[', @table_name),0) = 0) and
(isnull(charindex('%', @table_schema),0) = 0) and
(isnull(charindex('_', @table_schema),0) = 0) and
(isnull(charindex('[', @table_schema),0) = 0))
begin
select @fUsePattern = 0 -- not a single wild char, so go the fast way.
end
end

if @fUsePattern = 0
begin
-- -- Debug output, do not remove it.
print '-- -- -- -- -- -- *'
print 'No pattern matching.'
print @fUsePattern
print isnull(@table_name, '@table_name = null')
print isnull(@table_schema, '@table_schema = null')
print isnull(@column_name, '@column_name = null')
print '-- -- -- -- -- -- *'
--
insert into #tmp_columns
select
TABLE_CAT = c.TABLE_CATALOG,
TABLE_SCHEM = c.TABLE_SCHEMA,
TABLE_NAME = c.TABLE_NAME,
COLUMN_NAME = c.COLUMN_NAME,
DATA_TYPE = case c.DATA_TYPE & ~@DBTYPE_BYREF
when @DBTYPE_I2 then @ODBC_SMALLINT
when @DBTYPE_I4 then @ODBC_INT
when @DBTYPE_R4 then @ODBC_REAL
when @DBTYPE_R8 then @ODBC_FLOAT
when @DBTYPE_CY then @ODBC_MONEY
when @DBTYPE_DATE then @ODBC_DATETIME
when @DBTYPE_DBDATE then @ODBC_DATE
when @DBTYPE_DBTIME then @ODBC_DATETIME
when @DBTYPE_DBTIMESTAMP then @ODBC_DATETIME
when @DBTYPE_BOOL then @ODBC_BIT
when @DBTYPE_DECIMAL then @ODBC_DECIMAL
when @DBTYPE_UI1 then @ODBC_TINYINT
when @DBTYPE_I1 then @ODBC_NUMERIC
when @DBTYPE_UI2 then @ODBC_NUMERIC
when @DBTYPE_UI4 then @ODBC_NUMERIC
when @DBTYPE_I8 then @ODBC_NUMERIC
when @DBTYPE_UI8 then @ODBC_NUMERIC
when @DBTYPE_NUMERIC then @ODBC_NUMERIC
when @DBTYPE_VARNUMERIC then @ODBC_NUMERIC
when @DBTYPE_GUID then @ODBC_GUID
when @DBTYPE_DBTIME2 then @ODBC_TIME2
when @DBTYPE_DBTIMESTAMPOFFSET then @ODBC_DATETIMEOFFSET
when @DBTYPE_BYTES then
case
when ((c.COLUMN_FLAGS & @DBCOLUMNFLAGS_ISLONG = @DBCOLUMNFLAGS_ISLONG ) or (p.COLUMN_SIZE > 8000)) and
(c.CHARACTER_MAXIMUM_LENGTH <> 0) then @ODBC_IMAGE -- exclude varbinary(max) here and use default
when (c.COLUMN_FLAGS & @DBCOLUMNFLAGS_ISROWVER) = @DBCOLUMNFLAGS_ISROWVER and
(c.COLUMN_FLAGS & @DBCOLUMNFLAGS_ISFIXEDLENGTH) = @DBCOLUMNFLAGS_ISFIXEDLENGTH and
p.COLUMN_SIZE = 8
then @ODBC_TIMESTAMP
when (c.COLUMN_FLAGS & @DBCOLUMNFLAGS_ISFIXEDLENGTH = @DBCOLUMNFLAGS_ISFIXEDLENGTH)
then @ODBC_BINARY
else @ODBC_VARBINARY -- default
end
when @DBTYPE_STR then
case
when ((c.COLUMN_FLAGS & @DBCOLUMNFLAGS_ISLONG = @DBCOLUMNFLAGS_ISLONG ) or (p.COLUMN_SIZE > 8000)) and
(c.CHARACTER_MAXIMUM_LENGTH <> 0) then @ODBC_TEXT -- exclude varchar(max) here and use default
when (c.COLUMN_FLAGS & @DBCOLUMNFLAGS_ISFIXEDLENGTH = @DBCOLUMNFLAGS_ISFIXEDLENGTH) then @ODBC_CHAR
else @ODBC_VARCHAR -- default
end
when @DBTYPE_WSTR then
case
when ((c.COLUMN_FLAGS & @DBCOLUMNFLAGS_ISLONG = @DBCOLUMNFLAGS_ISLONG) or (p.COLUMN_SIZE > 4000)) and
(c.CHARACTER_MAXIMUM_LENGTH <> 0) then @ODBC_NTEXT -- exclude nvarchar(max) here and use default
when (c.COLUMN_FLAGS & @DBCOLUMNFLAGS_ISFIXEDLENGTH = @DBCOLUMNFLAGS_ISFIXEDLENGTH) then @ODBC_NCHAR
else @ODBC_NVARCHAR -- default
end
when @DBTYPE_BSTR then
case
when ((c.COLUMN_FLAGS & @DBCOLUMNFLAGS_ISLONG = @DBCOLUMNFLAGS_ISLONG) or (p.COLUMN_SIZE > 4000)) and
(c.CHARACTER_MAXIMUM_LENGTH <> 0) then @ODBC_NTEXT -- exclude nvarchar(max) here and use default
when (c.COLUMN_FLAGS & @DBCOLUMNFLAGS_ISFIXEDLENGTH = @DBCOLUMNFLAGS_ISFIXEDLENGTH) then @ODBC_NCHAR
else @ODBC_NVARCHAR -- default
end
when @DBTYPE_XML then @ODBC_NVARCHAR
else @ODBC_NVARCHAR -- Unknown OleDB datatype
end,
TYPE_NAME = p.TYPE_NAME,
COLUMN_SIZE = COALESCE(c.CHARACTER_MAXIMUM_LENGTH, c.NUMERIC_PRECISION, p.COLUMN_SIZE),
BUFFER_LENGTH = c.CHARACTER_OCTET_LENGTH,
DECIMAL_DIGITS = c.NUMERIC_SCALE,
NUM_PREC_RADIX = NULL,
NULLABLE = convert(smallint, c.IS_NULLABLE),
REMARKS = convert(nvarchar(254),c.DESCRIPTION),
COLUMN_DEF = convert(nvarchar(254),c.COLUMN_DEFAULT),
SQL_DATA_TYPE = NULL,
SQL_DATETIME_SUB = convert(smallint, c.DATETIME_PRECISION), -- temporary holder (to be updated later)
CHAR_OCTET_LENGTH = c.CHARACTER_OCTET_LENGTH,
ORDINAL_POSITION = c.ORDINAL_POSITION,
IS_NULLABLE = case c.IS_NULLABLE
when 1 then 'YES'
else 'NO'
end,
-- ISSUE - These columns are not exposed by engine.
-- ISSUE - See SQL BU 70074, which is postponed to Acadia!
SS_UDT_CATALOG_NAME = convert(sysname, null), -- c.SS_UDT_CATALOGNAME,
SS_UDT_SCHEMA_NAME = convert(sysname, null), -- c.SS_UDT_SCHEMANAME,
SS_UDT_ASSEMBLY_TYPE_NAME = convert(nvarchar(4000), null) collate Latin1_General_BIN, -- c.SS_UDT_ASSEMBLY_TYPENAME,
SS_XML_SCHEMACOLLECTION_CATALOG_NAME = convert(sysname, null), -- c.SS_XML_SCHEMACOLLECTION_CATALOGNAME,
SS_XML_SCHEMACOLLECTION_SCHEMA_NAME = convert(sysname, null), -- c.SS_XML_SCHEMACOLLECTION_SCHEMANAME,
SS_XML_SCHEMACOLLECTION_NAME = convert(sysname, null), -- c.SS_XML_SCHEMACOLLECTIONNAME,
--
SS_DATA_TYPE = null,
COLUMN_FLAGS = c.COLUMN_FLAGS
from
sys.fn_remote_columns (@table_server,@table_catalog,@table_schema,@table_name,NULL) c,
sys.fn_remote_provider_types (@table_server, NULL, NULL) p -- LUXOR/KAGERA don't support restricting DATA_TYPE
where
c.DATA_TYPE = p.DATA_TYPE and p.BEST_MATCH = 1
and
(
c.COLUMN_NAME like @column_name or
@column_name is NULL
)
end
else
begin
-- -- Debug output, do not remove it.
print '-- -- -- -- -- -- *'
print 'THERE IS pattern matching!'
print @fUsePattern
print isnull(@table_name, '@table_name = null')
print isnull(@table_schema, '@table_schema = null')
print isnull(@column_name, '@column_name = null')
print '-- -- -- -- -- -- *'
--
-- copy&pasted from above, but (1) and (2) are different
insert into #tmp_columns
select
TABLE_CAT = c.TABLE_CATALOG,
TABLE_SCHEM = c.TABLE_SCHEMA,
TABLE_NAME = c.TABLE_NAME,
COLUMN_NAME = c.COLUMN_NAME,
DATA_TYPE = case c.DATA_TYPE & ~@DBTYPE_BYREF
when @DBTYPE_I2 then @ODBC_SMALLINT
when @DBTYPE_I4 then @ODBC_INT
when @DBTYPE_R4 then @ODBC_REAL
when @DBTYPE_R8 then @ODBC_FLOAT
when @DBTYPE_CY then @ODBC_MONEY
when @DBTYPE_DATE then @ODBC_DATETIME
when @DBTYPE_DBDATE then @ODBC_DATE
when @DBTYPE_DBTIME then @ODBC_DATETIME
when @DBTYPE_DBTIMESTAMP then @ODBC_DATETIME
when @DBTYPE_BOOL then @ODBC_BIT
when @DBTYPE_DECIMAL then @ODBC_DECIMAL
when @DBTYPE_UI1 then @ODBC_TINYINT
when @DBTYPE_I1 then @ODBC_NUMERIC
when @DBTYPE_UI2 then @ODBC_NUMERIC
when @DBTYPE_UI4 then @ODBC_NUMERIC
when @DBTYPE_I8 then @ODBC_NUMERIC
when @DBTYPE_UI8 then @ODBC_NUMERIC
when @DBTYPE_NUMERIC then @ODBC_NUMERIC
when @DBTYPE_VARNUMERIC then @ODBC_NUMERIC
when @DBTYPE_GUID then @ODBC_GUID
when @DBTYPE_DBTIME2 then @ODBC_TIME2
when @DBTYPE_DBTIMESTAMPOFFSET then @ODBC_DATETIMEOFFSET
when @DBTYPE_BYTES then
case
when ((c.COLUMN_FLAGS & @DBCOLUMNFLAGS_ISLONG = @DBCOLUMNFLAGS_ISLONG ) or (p.COLUMN_SIZE > 8000)) and
(c.CHARACTER_MAXIMUM_LENGTH <> 0) then @ODBC_IMAGE -- exclude varbinary(max) here and use default
when (c.COLUMN_FLAGS & @DBCOLUMNFLAGS_ISROWVER) = @DBCOLUMNFLAGS_ISROWVER and
(c.COLUMN_FLAGS & @DBCOLUMNFLAGS_ISFIXEDLENGTH) = @DBCOLUMNFLAGS_ISFIXEDLENGTH and
p.COLUMN_SIZE = 8
then @ODBC_TIMESTAMP
when (c.COLUMN_FLAGS & @DBCOLUMNFLAGS_ISFIXEDLENGTH = @DBCOLUMNFLAGS_ISFIXEDLENGTH)
then @ODBC_BINARY
else @ODBC_VARBINARY -- default
end
when @DBTYPE_STR then
case
when ((c.COLUMN_FLAGS & @DBCOLUMNFLAGS_ISLONG = @DBCOLUMNFLAGS_ISLONG ) or (p.COLUMN_SIZE > 8000)) and
(c.CHARACTER_MAXIMUM_LENGTH <> 0) then @ODBC_TEXT -- exclude varchar(max) here and use default
when (c.COLUMN_FLAGS & @DBCOLUMNFLAGS_ISFIXEDLENGTH = @DBCOLUMNFLAGS_ISFIXEDLENGTH) then @ODBC_CHAR
else @ODBC_VARCHAR -- default
end
when @DBTYPE_WSTR then
case
when ((c.COLUMN_FLAGS & @DBCOLUMNFLAGS_ISLONG = @DBCOLUMNFLAGS_ISLONG) or (p.COLUMN_SIZE > 4000)) and
(c.CHARACTER_MAXIMUM_LENGTH <> 0) then @ODBC_NTEXT -- exclude nvarchar(max) here and use default
when (c.COLUMN_FLAGS & @DBCOLUMNFLAGS_ISFIXEDLENGTH = @DBCOLUMNFLAGS_ISFIXEDLENGTH) then @ODBC_NCHAR
else @ODBC_NVARCHAR -- default
end
when @DBTYPE_BSTR then
case
when ((c.COLUMN_FLAGS & @DBCOLUMNFLAGS_ISLONG = @DBCOLUMNFLAGS_ISLONG) or (p.COLUMN_SIZE > 4000)) and
(c.CHARACTER_MAXIMUM_LENGTH <> 0) then @ODBC_NTEXT -- exclude nvarchar(max) here and use default
when (c.COLUMN_FLAGS & @DBCOLUMNFLAGS_ISFIXEDLENGTH = @DBCOLUMNFLAGS_ISFIXEDLENGTH) then @ODBC_NCHAR
else @ODBC_NVARCHAR -- default
end
when @DBTYPE_XML then @ODBC_NVARCHAR
else @ODBC_NVARCHAR -- Unknown OleDB datatype
end,
TYPE_NAME = p.TYPE_NAME,
COLUMN_SIZE = COALESCE(c.CHARACTER_MAXIMUM_LENGTH, c.NUMERIC_PRECISION, p.COLUMN_SIZE),
BUFFER_LENGTH = c.CHARACTER_OCTET_LENGTH,
DECIMAL_DIGITS = c.NUMERIC_SCALE,
NUM_PREC_RADIX = NULL,
NULLABLE = convert(smallint, c.IS_NULLABLE),
REMARKS = convert(nvarchar(254),c.DESCRIPTION),
COLUMN_DEF = convert(nvarchar(254),c.COLUMN_DEFAULT),
SQL_DATA_TYPE = NULL,
SQL_DATETIME_SUB = convert(smallint, c.DATETIME_PRECISION), -- temporary holder (to be updated later)
CHAR_OCTET_LENGTH = c.CHARACTER_OCTET_LENGTH,
ORDINAL_POSITION = c.ORDINAL_POSITION,
IS_NULLABLE = case c.IS_NULLABLE
when 1 then 'YES'
else 'NO'
end,
-- ISSUE - These columns are not exposed by engine.
-- ISSUE - See SQL BU 70074, which is postponed to Acadia!
SS_UDT_CATALOG_NAME = convert(sysname, null), -- c.SS_UDT_CATALOGNAME,
SS_UDT_SCHEMA_NAME = convert(sysname, null), -- c.SS_UDT_SCHEMANAME,
SS_UDT_ASSEMBLY_TYPE_NAME = convert(nvarchar(4000), null) collate Latin1_General_BIN, -- c.SS_UDT_ASSEMBLY_TYPENAME,
SS_XML_SCHEMACOLLECTION_CATALOG_NAME = convert(sysname, null), -- c.SS_XML_SCHEMACOLLECTION_CATALOGNAME,
SS_XML_SCHEMACOLLECTION_SCHEMA_NAME = convert(sysname, null), -- c.SS_XML_SCHEMACOLLECTION_SCHEMANAME,
SS_XML_SCHEMACOLLECTION_NAME = convert(sysname, null), -- c.SS_XML_SCHEMACOLLECTIONNAME,
--
SS_DATA_TYPE = null,
COLUMN_FLAGS = c.COLUMN_FLAGS
from
sys.fn_remote_columns (@table_server,@table_catalog,NULL,NULL,NULL) c, -- (1)
sys.fn_remote_provider_types (@table_server, NULL, NULL) p -- LUXOR/KAGERA don't support restricting DATA_TYPE
where
c.DATA_TYPE = p.DATA_TYPE and p.BEST_MATCH = 1 and
(
TABLE_SCHEMA like @table_schema or
@table_schema is NULL or
(TABLE_SCHEMA is NULL and @table_schema = N'%')
)
and
(
c.COLUMN_NAME like @column_name or
@column_name is NULL
) and
c.TABLE_NAME like @table_name -- (2)
end

--
-- Converts OLEDB types to ODBC types.
--
update #tmp_columns
set
DATA_TYPE = case
when #tmp_columns.DATA_TYPE = @ODBC_DATETIME and
(COLUMN_FLAGS & @DBCOLUMNFLAGS_SS_ISVARIABLESCALE) = @DBCOLUMNFLAGS_SS_ISVARIABLESCALE
then @ODBC_NVARCHAR
when #tmp_columns.DATA_TYPE IN (@ODBC_DATE, @ODBC_TIME2, @ODBC_DATETIMEOFFSET)
then @ODBC_NVARCHAR
else #tmp_columns.DATA_TYPE
end,
TYPE_NAME = case
when #tmp_columns.DATA_TYPE = @ODBC_BINARY and
(COLUMN_FLAGS & @DBCOLUMNFLAGS_ISROWVER) = @DBCOLUMNFLAGS_ISROWVER and
(COLUMN_FLAGS & @DBCOLUMNFLAGS_ISFIXEDLENGTH) = @DBCOLUMNFLAGS_ISFIXEDLENGTH and
BUFFER_LENGTH = 8
then 'timestamp'
when #tmp_columns.DATA_TYPE = @ODBC_DATETIME and
(COLUMN_FLAGS & @DBCOLUMNFLAGS_SS_ISVARIABLESCALE) = @DBCOLUMNFLAGS_SS_ISVARIABLESCALE
then 'datetime2'
when #tmp_columns.DATA_TYPE = @ODBC_DATETIME and
#tmp_columns.SQL_DATETIME_SUB = 3
then 'datetime'
when #tmp_columns.DATA_TYPE = @ODBC_DATETIME and
#tmp_columns.SQL_DATETIME_SUB = 0
then 'smalldatetime'
when #tmp_columns.DATA_TYPE = @ODBC_MONEY and
#tmp_columns.COLUMN_SIZE = 10
then 'smallmoney'
when (#tmp_columns.DATA_TYPE in (@ODBC_BINARY, @ODBC_CHAR) and
(COLUMN_FLAGS & @DBCOLUMNFLAGS_ISFIXEDLENGTH) = @DBCOLUMNFLAGS_ISFIXEDLENGTH) or
#tmp_columns.DATA_TYPE in (@ODBC_TEXT, @ODBC_NTEXT, @ODBC_IMAGE)
then spt_dt.TYPE_NAME collate catalog_default
else isnull(#tmp_columns.TYPE_NAME, spt_dt.TYPE_NAME collate catalog_default)
end,
COLUMN_SIZE = case
when #tmp_columns.DATA_TYPE IN (@ODBC_REAL, @ODBC_FLOAT) then -- app. numeric types
spt_dt.data_precision
when #tmp_columns.DATA_TYPE = @ODBC_DATETIME and
(COLUMN_FLAGS & @DBCOLUMNFLAGS_SS_ISVARIABLESCALE) = @DBCOLUMNFLAGS_SS_ISVARIABLESCALE
then
case
when #tmp_columns.SQL_DATETIME_SUB = 0
then 19
else
(20 + #tmp_columns.SQL_DATETIME_SUB)
end
when #tmp_columns.DATA_TYPE = @ODBC_DATETIME and
#tmp_columns.SQL_DATETIME_SUB = 0
then 16
when #tmp_columns.DATA_TYPE = @ODBC_DATETIME and
#tmp_columns.SQL_DATETIME_SUB = 3
then 23
when #tmp_columns.DATA_TYPE = @ODBC_TIME2
then
case
when #tmp_columns.SQL_DATETIME_SUB = 0
then 8
else
(9 + #tmp_columns.SQL_DATETIME_SUB)
end
when #tmp_columns.DATA_TYPE = @ODBC_DATETIMEOFFSET
then
case
when #tmp_columns.SQL_DATETIME_SUB = 0
then 26
else
(27 + #tmp_columns.SQL_DATETIME_SUB)
end
else #tmp_columns.COLUMN_SIZE
end,
BUFFER_LENGTH = case
when #tmp_columns.DATA_TYPE IN (@ODBC_NUMERIC, @ODBC_DECIMAL) then -- decimal/numeric types
COLUMN_SIZE+2
when #tmp_columns.DATA_TYPE = @ODBC_DATETIME and
(COLUMN_FLAGS & @DBCOLUMNFLAGS_SS_ISVARIABLESCALE) = @DBCOLUMNFLAGS_SS_ISVARIABLESCALE
then
case
when #tmp_columns.SQL_DATETIME_SUB = 0
then 38 -- 19*2
else
(20 + #tmp_columns.SQL_DATETIME_SUB)*2
end
when #tmp_columns.DATA_TYPE = @ODBC_DATE
then 20
when #tmp_columns.DATA_TYPE = @ODBC_TIME2
then
case
when #tmp_columns.SQL_DATETIME_SUB = 0
then 16 -- 8*2
else
(9 + #tmp_columns.SQL_DATETIME_SUB)*2
end
when #tmp_columns.DATA_TYPE = @ODBC_DATETIMEOFFSET
then
case
when #tmp_columns.SQL_DATETIME_SUB = 0
then 52 -- 26*2
else
(27 + #tmp_columns.SQL_DATETIME_SUB)*2
end
when isnull (#tmp_columns.BUFFER_LENGTH,0) = 0 then coalesce(spt_dt.length, spt_dt.data_precision)
else #tmp_columns.BUFFER_LENGTH
end,
DECIMAL_DIGITS = case
when #tmp_columns.DATA_TYPE = @ODBC_DATETIME and
(COLUMN_FLAGS & @DBCOLUMNFLAGS_SS_ISVARIABLESCALE) = 0
then #tmp_columns.SQL_DATETIME_SUB
when #tmp_columns.DATA_TYPE IN (@ODBC_DATETIME, @ODBC_DATE, @ODBC_TIME2, @ODBC_DATETIMEOFFSET)
then NULL
else COALESCE (DECIMAL_DIGITS, spt_dt.numeric_scale)
end,
NUM_PREC_RADIX = spt_dt.RADIX,
SQL_DATA_TYPE = case
when #tmp_columns.DATA_TYPE = @ODBC_DATETIME and
(COLUMN_FLAGS & @DBCOLUMNFLAGS_SS_ISVARIABLESCALE) = @DBCOLUMNFLAGS_SS_ISVARIABLESCALE
then @ODBC_NVARCHAR
when #tmp_columns.DATA_TYPE IN (@ODBC_DATE, @ODBC_TIME2, @ODBC_DATETIMEOFFSET)
then @ODBC_NVARCHAR
else spt_dt.SQL_DATA_TYPE
end,
SQL_DATETIME_SUB = case -- used to be temporary holder for DATETIME_PRECISION, update it here
when #tmp_columns.DATA_TYPE in (@ODBC_DATETIME) and
(COLUMN_FLAGS & @DBCOLUMNFLAGS_SS_ISVARIABLESCALE) = 0
then 3 -- SQL_CODE_TIMESTAMP
else null
end,
-- Info here is for backward compatibility - SQL 6.5
SS_DATA_TYPE = case
when #tmp_columns.DATA_TYPE in ( @ODBC_TINYINT, @ODBC_SMALLINT, @ODBC_INT )
and #tmp_columns.NULLABLE = 1
then @ODS_INT_NULL
when #tmp_columns.DATA_TYPE = @ODBC_TINYINT and #tmp_columns.NULLABLE = 0
then @ODS_TINYINT_NOT_NULL
when #tmp_columns.DATA_TYPE = @ODBC_SMALLINT and #tmp_columns.NULLABLE = 0
then @ODS_SMALLINT_NOT_NULL
when #tmp_columns.DATA_TYPE = @ODBC_INT and #tmp_columns.NULLABLE = 0
then @ODS_INT_NOT_NULL
when #tmp_columns.DATA_TYPE in (@ODBC_REAL, @ODBC_FLOAT) and #tmp_columns.NULLABLE = 1
then @ODS_FLOAT_NULL
when #tmp_columns.DATA_TYPE = @ODBC_REAL and #tmp_columns.NULLABLE = 0
then @ODS_REAL_NOT_NULL
when #tmp_columns.DATA_TYPE = @ODBC_FLOAT and #tmp_columns.NULLABLE = 0
then @ODS_FLOAT_NOT_NULL
when #tmp_columns.DATA_TYPE = @ODBC_MONEY and #tmp_columns.NULLABLE = 1
then @ODS_MONEY_NULL
when #tmp_columns.DATA_TYPE = @ODBC_MONEY and #tmp_columns.NULLABLE = 0
then @ODS_MONEY_NOT_NULL
-- New date/time types introduced in Katmai return 0
when #tmp_columns.DATA_TYPE = @ODBC_DATETIME and
(COLUMN_FLAGS & @DBCOLUMNFLAGS_SS_ISVARIABLESCALE) = @DBCOLUMNFLAGS_SS_ISVARIABLESCALE
then 0
when #tmp_columns.DATA_TYPE in (@ODBC_DATE, @ODBC_TIME2, @ODBC_DATETIMEOFFSET)
then 0
when #tmp_columns.DATA_TYPE = @ODBC_DATETIME and #tmp_columns.NULLABLE = 1
then @ODS_DATETIME_NULL
when #tmp_columns.DATA_TYPE = @ODBC_DATETIME and #tmp_columns.NULLABLE = 0
then @ODS_DATETIME_NOT_NULL
when #tmp_columns.DATA_TYPE in (@ODBC_NTEXT, @ODBC_TEXT)
then @ODS_TEXT
when #tmp_columns.DATA_TYPE = @ODBC_BIT
then @ODS_BIT
when #tmp_columns.DATA_TYPE = @ODBC_DECIMAL
then @ODS_DECIMAL
when #tmp_columns.DATA_TYPE = @ODBC_NUMERIC
then @ODS_NUMERIC
when #tmp_columns.DATA_TYPE = @ODBC_GUID
then @ODS_GUID
when #tmp_columns.DATA_TYPE = @ODBC_IMAGE
then @ODS_IMAGE
when #tmp_columns.DATA_TYPE = @ODBC_TIMESTAMP
and (COLUMN_FLAGS & @DBCOLUMNFLAGS_ISROWVER) = @DBCOLUMNFLAGS_ISROWVER and
(COLUMN_FLAGS & @DBCOLUMNFLAGS_ISFIXEDLENGTH) = @DBCOLUMNFLAGS_ISFIXEDLENGTH and
BUFFER_LENGTH = 8
then @ODS_TIMESTAMP
when #tmp_columns.DATA_TYPE = @ODBC_BINARY and #tmp_columns.NULLABLE = 1
and (COLUMN_FLAGS & @DBCOLUMNFLAGS_ISFIXEDLENGTH = @DBCOLUMNFLAGS_ISFIXEDLENGTH)
then @ODS_BINARY_NULL
when #tmp_columns.DATA_TYPE = @ODBC_BINARY and #tmp_columns.NULLABLE = 0
and (COLUMN_FLAGS & @DBCOLUMNFLAGS_ISFIXEDLENGTH = @DBCOLUMNFLAGS_ISFIXEDLENGTH)
then @ODS_BINARY_NOT_NULL
when #tmp_columns.DATA_TYPE = @ODBC_VARBINARY
then @ODS_VARBINARY
when #tmp_columns.DATA_TYPE in (@ODBC_CHAR, @ODBC_NCHAR) and #tmp_columns.NULLABLE = 1
then @ODS_CHAR_NULL
when #tmp_columns.DATA_TYPE in (@ODBC_CHAR, @ODBC_NCHAR) and #tmp_columns.NULLABLE = 0
then @ODS_CHAR_NOT_NULL
when #tmp_columns.DATA_TYPE in (@ODBC_VARCHAR, @ODBC_NVARCHAR)
then @ODS_VARCHAR
else null
end
from
sys.spt_datatype_info spt_dt
where
spt_dt.DATA_TYPE = #tmp_columns.DATA_TYPE and
spt_dt.ODBCVer = @ODBCVer and
spt_dt.AUTO_INCREMENT = 0 and
spt_dt.TYPE_NAME <> 'smalldatetime' and
spt_dt.TYPE_NAME <> 'xml' -- DATA_TYPE for ntext and xml are the same, so allow just ntext here.
--
-- Final result is retunrned from the temporary table.
--
select
TABLE_CAT,
TABLE_SCHEM,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
TYPE_NAME,
COLUMN_SIZE,
BUFFER_LENGTH,
DECIMAL_DIGITS,
NUM_PREC_RADIX,
NULLABLE,
REMARKS,
COLUMN_DEF,
SQL_DATA_TYPE,
SQL_DATETIME_SUB,
CHAR_OCTET_LENGTH,
ORDINAL_POSITION,
IS_NULLABLE,
SS_UDT_CATALOG_NAME,
SS_UDT_SCHEMA_NAME,
SS_UDT_ASSEMBLY_TYPE_NAME,
SS_XML_SCHEMACOLLECTION_CATALOG_NAME,
SS_XML_SCHEMACOLLECTION_SCHEMA_NAME,
SS_XML_SCHEMACOLLECTION_NAME,
SS_DATA_TYPE
from
#tmp_columns
order by TABLE_CAT, TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION

drop table #tmp_columns

No comments:

Post a Comment

Total Pageviews