Post reply

Syscolumns does'nt return User Tables

  • May 7, 2008 at 10:55 pm

    #185194

    Hi,

    I am trying to fetch the Columns for a User defined table.Using Query

    I am executing this from a different database

    SELECT * FROM SAMPLEDB..SYSCOLUMNS

    WHERE OBJECT_NAME(ID)='Test_Tbl'

    It doesn't return any rows.But if i execute the query in that particular Database it is returning the resultsets...Can anyone help me please

  • May 7, 2008 at 11:06 pm

    #812217

    The object_name and object_ID functions work only in the current database.

    For what you're trying to do, try joining the sys.columns and sys.tables views together.

    SELECT * from OtherDB.sys.tables t inner join otherDB.sys.columns c on t.object_id = c.object_id

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 8, 2008 at 12:36 am

    #812238

    Perfect! Thanks a lot!:)

  • May 8, 2008 at 12:42 am

    #812243

    SELECT * FROM INFORMATION_SCHEMA.COLUMNS

    N 56°04'39.16"
    E 12°55'05.25"

Viewing 4 posts - 1 through 3 (of 3 total)

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