Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_columns_ex_100

  No additional text.


Syntax

create procedure sys.sp_columns_ex_100
(
    @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 database_default NULL,
        TABLE_SCHEM sysname collate database_default NULL,
        TABLE_NAME sysname  collate database_default NOT NULL,
        COLUMN_NAME sysname collate database_default NULL,
        DATA_TYPE smallint NOT NULL,
        TYPE_NAME sysname  collate database_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 database_default NULL,
        COLUMN_DEF nvarchar(254) collate database_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 database_default NOT NULL,
        SS_IS_SPARSE smallint NULL,
        SS_IS_COLUMN_SET smallint NULL,
        SS_IS_COMPUTED smallint,
        SS_IS_IDENTITY smallint,
        SS_UDT_CATALOG_NAME sysname collate database_default NULL,
        SS_UDT_SCHEMA_NAME sysname collate database_default NULL,
        SS_UDT_ASSEMBLY_TYPE_NAME nvarchar(4000) collate Latin1_General_BIN NULL,
        SS_XML_SCHEMACOLLECTION_CATALOG_NAME sysname collate database_default NULL,
        SS_XML_SCHEMACOLLECTION_SCHEMA_NAME sysname collate database_default NULL,
        SS_XML_SCHEMACOLLECTION_NAME sysname collate database_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_IS_SPARSE                         = convert(smallint, 0),
            SS_IS_COLUMN_SET                     = convert(smallint, 0),
            SS_IS_COMPUTED                       = convert(smallint, 0),
            SS_IS_IDENTITY                       = convert(smallint, 0),
            SS_UDT_CATALOG_NAME  = convert(sysname, 0), -- c.SS_UDT_CATALOGNAME,
            SS_UDT_SCHEMA_NAME                   = convert(sysname, 0), -- 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_IS_SPARSE                         = convert(smallint, 0),
            SS_IS_COLUMN_SET                     = convert(smallint, 0),
            SS_IS_COMPUTED                       = convert(smallint, 0),
            SS_IS_IDENTITY                       = convert(smallint, 0),
            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
        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 database_default
                                      else isnull(#tmp_columns.TYPE_NAME, spt_dt.TYPE_NAME collate database_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 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 or
                                           #tmp_columns.DATA_TYPE = @ODBC_TIME2 or
                                           #tmp_columns.DATA_TYPE = @ODBC_DATETIMEOFFSET or
                                           #tmp_columns.DATA_TYPE = @ODBC_DATE
                                          then #tmp_columns.SQL_DATETIME_SUB
                                      else COALESCE (DECIMAL_DIGITS, spt_dt.numeric_scale)
                                  end,
        NUM_PREC_RADIX          = spt_dt.RADIX,
        SQL_DATA_TYPE           = spt_dt.SQL_DATA_TYPE,
        SQL_DATETIME_SUB        = case -- used to be temporary holder for DATETIME_PRECISION, update it here
                                      when #tmp_columns.DATA_TYPE = @ODBC_DATETIME
                                            then 3 -- SQL_CODE_TIMESTAMP
                                      when #tmp_columns.DATA_TYPE = @ODBC_DATE
                                            then 1 -- SQL_CODE_DATE
                                      when #tmp_columns.DATA_TYPE in (@ODBC_TIME2, @ODBC_DATETIMEOFFSET)
                                            then 0
                                      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_datatype_info has a clustered index on ss_dtype so we should use always that
            -- rather than type name
            spt_dt.ss_dtype <> 58 and -- smalldatetime
            spt_dt.ss_dtype <> 61 and -- datetime
            spt_dt.ss_dtype <> 241    -- 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_IS_SPARSE,
        SS_IS_COLUMN_SET,
        SS_IS_COMPUTED,
        SS_IS_IDENTITY,
        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


 
Last revision 2008RTM
See also

  sp_ddopen (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