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