syscolumns versus information_schema.columns issue

• maroon-78

  Ten Centuries

  Points: 1014

  More actions

  May 31, 2012 at 9:00 am

  #261942

  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: 442467

  More actions

  May 31, 2012 at 9:09 am

  #1495306

  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

  More actions

  May 31, 2012 at 9:12 am

  #1495311

  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

  More actions

  May 31, 2012 at 9:13 am

  #1495313

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