syscolumns versus information_schema.columns issue

  • maroon-78

    Ten Centuries

    Points: 1014

    I am getting different results for two queries about a table column.

    when I run the following query against syscolumns:

    Select so.name,so.xtype ,sc.name,sc.xtype from sysobjects so

    Join syscolumns sc on sc.id = so.id

    Where so.name = 'abn_'

    And sc.name = 'person_id'

    I get no rows

    when I run

    select * from information_schema.columns

    where table_name = 'abn_'

    and column_name = 'person_id'

    I get the row for abn_.person_id

    Do I have a corrupt database? And how can I fix it?

  • Lynn Pettis

    SSC Guru

    Points: 442334

    Not being ale to look at what is in your system, not sure. One thing I would suggest, however, is to not use sysobjects and syscolumns. These are for backward compatibility. You should start using the new system views; sys.objects, sys.tables, sys.columns, etc.

  • chrisfradenburg

    SSCrazy Eights

    Points: 9592

    I'm not sure what would cause that but if you want to see if you have a corrupt DB run dbcc checkdb.

  • maroon-78

    Ten Centuries

    Points: 1014

    it runs fine on my SS2012 and SS2008R2 dev databases. It just doesn't run on production. 🙁

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

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