Getting the columns

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714600

    Comments posted to this topic are about the item Getting the columns

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71251

    Nice question to end the week on, thanks, Steve.

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    โ€œlibera tute vulgaris exโ€

  • HappyGeek

    SSCoach

    Points: 18650

    Great question, thanks Steve.

    ...

  • Arno.Hubert.Janssen

    SSCrazy

    Points: 2192

    If the current user owns an object with the specified name, the columns of that object are returned. If owner is not specified and the current user does not own an object with the specified object, sp_columns looks for an object with the specified object owned by the database owner. If one exists, that object's columns are returned.

    What am I missing? If the user owns an object by that name it is displayed, otherwise if the specific exists but is owned by the database owner (here I presume dbo), then that is displayed.

  • joseph.morgan

    Newbie

    Points: 5

    You might note that you will get an empty recordset if you are not connected to the database containing the 'Articles' table. No error, but no results. Switching to the correct database in your query editor will give the result desired.

  • Arno.Hubert.Janssen

    SSCrazy

    Points: 2192

    @joseph.Morgan - Thank you!!!

  • pamam

    Newbie

    Points: 2

    Actually wouldn't you just get a list of the column names not an empty record set, since the request only asks for the columns not any data per say. A correctly formatted SQL engine should not give you any more.

  • Eirikur Eiriksson

    SSC Guru

    Points: 182343

    The correct answer is not one of the options!

    ๐Ÿ˜Ž

    Hint: Multiple tables with the same name, each in a different schema

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714600

    This has to do with schemas, not necessarily owners. If you have a default schema of "etl" and there is only an dbo.Articles table, you don't get columns, but an empty set. If you have a default schema of "etl" and there is an "etl.Articles" table, you get that data. If you don't specify the schema, you get the view of a table in your schema if you have rights and it exists. If no table is there, you get an empty result.

    Probably a slightly confusing wording, but the test here was on schema. I'll reword the question to make this more clear.

  • Eirikur Eiriksson

    SSC Guru

    Points: 182343

    Authorization on the schema will still bring it into view

    ๐Ÿ˜Ž

    Currently there are 14 different versions of the sproc, here is the one inherited from sybase

    ALTER procedure [sys].[sp_columns]
    (
    @table_name nvarchar(384),
    @table_owner nvarchar(384) = null,
    @table_qualifier sysname = null,
    @column_name nvarchar(384) = null,
    @ODBCVer int = 2
    )
    as
    declare @full_table_name nvarchar(769) -- 384 + 1 + 384
    declare @table_id int
    declare @fUsePattern bit

    select @fUsePattern = 1

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

    if @table_qualifier is not null
    begin
    if db_name() <> @table_qualifier
    begin -- If qualifier doesn't match current database
    raiserror (15250, -1,-1)
    return
    end
    end

    -- "ALL" is represented by NULL value.
    if @table_name = '%'
    select @table_name = null
    if @table_owner = '%'
    select @table_owner = null
    if @table_qualifier = '%'
    select @table_qualifier = null
    if @column_name = '%'
    select @column_name = null

    -- Empty string means nothing, so use invalid identifier.
    -- A quoted space will never match any object name.
    if @table_owner = ''
    select @table_owner = ' '

    select @full_table_name = isnull(quotename(@table_owner), '') + '.' + isnull(quotename(@table_name), '')
    select @table_id = object_id(@full_table_name)

    if (@fUsePattern = 1) -- Does the user want it?
    begin
    if ((isnull(charindex('%', @full_table_name),0) = 0) and
    (isnull(charindex('_', @full_table_name),0) = 0) and
    (isnull(charindex('[', @table_name),0) = 0) and
    (isnull(charindex('[', @table_owner),0) = 0) and
    (isnull(charindex('%', @column_name),0) = 0) and
    (isnull(charindex('_', @column_name),0) = 0) and
    (@table_id <> 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(convert(sysname, @table_id), '@table_id = null')
    print isnull(@full_table_name, '@full_table_name = null')
    print isnull(@table_owner, '@table_owner = null')
    print isnull(@table_name, '@table_name = null')
    print isnull(@column_name, '@column_name = null')
    print '*************'
    */
    select
    TABLE_QUALIFIER = s_cov.TABLE_QUALIFIER,
    TABLE_OWNER = s_cov.TABLE_OWNER,
    TABLE_NAME = s_cov.TABLE_NAME,
    COLUMN_NAME = s_cov.COLUMN_NAME,
    DATA_TYPE = s_cov.DATA_TYPE_28,
    TYPE_NAME = s_cov.TYPE_NAME_28,
    "PRECISION" = s_cov.PRECISION_28,
    "LENGTH" = s_cov.LENGTH_28,
    SCALE = s_cov.SCALE_90,
    RADIX = s_cov.RADIX,
    NULLABLE = s_cov.NULLABLE,
    REMARKS = s_cov.REMARKS,
    COLUMN_DEF = s_cov.COLUMN_DEF,
    SQL_DATA_TYPE = s_cov.SQL_DATA_TYPE_28,
    SQL_DATETIME_SUB = s_cov.SQL_DATETIME_SUB_90,
    CHAR_OCTET_LENGTH = s_cov.CHAR_OCTET_LENGTH_28,
    ORDINAL_POSITION = s_cov.ORDINAL_POSITION,
    IS_NULLABLE = s_cov.IS_NULLABLE,
    SS_DATA_TYPE = s_cov.SS_DATA_TYPE

    from
    sys.spt_columns_odbc_view s_cov

    where
    s_cov.object_id = @table_id -- (2nd) (@table_name is null or o.name like @table_name)
    -- (2nd) and (@table_owner is null or schema_name(o.schema_id) like @table_owner)
    and (@column_name is null or s_cov.COLUMN_NAME = @column_name) -- (2nd) and (@column_name is NULL or c.name like @column_name)
    and s_cov.ODBCVER = @ODBCVer
    and s_cov.OBJECT_TYPE <> 'TT'
    and ( s_cov.SS_IS_SPARSE = 0 OR objectproperty ( s_cov.OBJECT_ID, 'tablehascolumnset' ) = 0 )
    order by 17
    end
    else
    begin
    /* -- Debug output, do not remove it.
    print '*************'
    print 'THERE IS pattern matching!'
    print @fUsePattern
    print isnull(convert(sysname, @table_id), '@table_id = null')
    print isnull(@full_table_name, '@full_table_name = null')
    print isnull(@table_owner, '@table_owner = null')
    print isnull(@table_name, '@table_name = null')
    print isnull(@column_name, '@column_name = null')
    print '*************'
    */
    select
    TABLE_QUALIFIER = s_cov.TABLE_QUALIFIER,
    TABLE_OWNER = s_cov.TABLE_OWNER,
    TABLE_NAME = s_cov.TABLE_NAME,
    COLUMN_NAME = s_cov.COLUMN_NAME,
    DATA_TYPE = s_cov.DATA_TYPE_28,
    TYPE_NAME = s_cov.TYPE_NAME_28,
    "PRECISION" = s_cov.PRECISION_28,
    "LENGTH" = s_cov.LENGTH_28,
    SCALE = s_cov.SCALE_90,
    RADIX = s_cov.RADIX,
    NULLABLE = s_cov.NULLABLE,
    REMARKS = s_cov.REMARKS,
    COLUMN_DEF = s_cov.COLUMN_DEF,
    SQL_DATA_TYPE = s_cov.SQL_DATA_TYPE_28,
    SQL_DATETIME_SUB = s_cov.SQL_DATETIME_SUB_90,
    CHAR_OCTET_LENGTH = s_cov.CHAR_OCTET_LENGTH_28,
    ORDINAL_POSITION = s_cov.ORDINAL_POSITION,
    IS_NULLABLE = s_cov.IS_NULLABLE,
    SS_DATA_TYPE = s_cov.SS_DATA_TYPE

    from
    sys.spt_columns_odbc_view s_cov

    where
    s_cov.ODBCVER = @ODBCVer and
    s_cov.OBJECT_TYPE <> 'TT' and
    (@table_name is null or s_cov.TABLE_NAME like @table_name) and
    (@table_owner is null or schema_name(s_cov.SCHEMA_ID) like @table_owner) and
    (@column_name is null or s_cov.COLUMN_NAME like @column_name) and
    ( s_cov.SS_IS_SPARSE = 0 OR objectproperty ( s_cov.OBJECT_ID, 'tablehascolumnset' ) = 0 )

    order by 2, 3, 17
    end
  • rVadim

    Hall of Fame

    Points: 3865

    Also, if I'm not mistaken, default schema is ignored for sysadmins. They always get dbo.

    --Vadim R.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply