Post reply

syscolumns index problem

  • November 9, 2005 at 10:31 am

    #92020

    I am finding that when i query the syscolumns table and it uses the index ID defined for that table the query returns no rows, however, if I run the same query but force the index to SYSCOLUMNS it returns the expected results.

    ie.

    run this...

    select colid from syscolumns where id = 1225107455 and colid = 6

    I get no results

    run this...

    select colid from syscolumns with (index(syscolumns)) where id = 1225107455 and colid = 6

    I get a result returned as expected.

    The row in syscolumns does exist, so it seems like an index problem, has anyone seen anything similar ?

    Is there anyway to rebuild a system index ?

    It is causing us a problem when using linked servers as they use sp_columns_rowset to fetch metadata, which uses the colid column.

    Thanks.

  • November 14, 2005 at 8:27 am

    #603693

    I am not sure why the id is being used instead of the table name (readability!!!)...but I tried the same query using both id as well as name and got back expected results - sorry - could not recreate the problem...

    ..using Northwind

    select * from syscolumns
where id = 2041058307--object_id('categories')
and colid = 2

select * from information_schema.columns
where table_name = 'categories'
and ordinal_position = 2







    **ASCII stupid question, get a stupid ANSI !!!**

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

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