Table not showing PK - PK is in sysindexes ?

  • I've got a table Investing Fund Prices that I noticed had no primary key defined.

    I have a script for the PK,

    ALTER TABLE [dbo].[Investing Fund Prices] ADD

    CONSTRAINT [aaaaaInvesting Fund Prices_PK] PRIMARY KEY NONCLUSTERED

    (

    [Fund_No],

    [Date]

    ) WITH FILLFACTOR = 90 ON [PRIMARY]

    GO

    However, when I run this script, I get

    Server: Msg 2714, Level 16, State 4, Line 1

    There is already an object named 'aaaaaInvesting Fund Prices_PK' in the database.

    Server: Msg 1750, Level 16, State 1, Line 1

    Could not create constraint. See previous errors.

    Now, I looked in sysindexes and there's already a row there for the PK 'aaaaaInvesting Fund Prices_PK'

    idstatusfirstindidrootminlenkeycntgroupiddpagesreservedusedrowcntrowmodctrreserved3reserved4xmaxlenmaxirowOrigFillFactorStatVersionreserved2FirstIAMimpidlockflagspgmodctrkeysnamestatblobmaxlenrows

    154858460520500x00000000000030x00000000000019310000-95400364290000x0000000000000000x34013400020005000000000000000000010001000000000004000100000000003D013D0008001703000000000000000002000300000000000600030000000000AD01AD0008000000000000000000000000000B000000000200000B0000000100aaaaaInvesting Fund Prices_PKNULL80000

    However when I script the table (or Modify in SQL EM), the primary key doesn't show.

    Can anyone tell me what's going on ? Or how to find out which table an index belongs to ?

    Much obliged.

  • I'll bet that index exists on a different table. I've got a script at home to find indexes and the associated table. If noone has supplied it to you by the time I get home, I'll try to remember to send it your way.

    run: select * from sysindexes where name = 'aaaaaInvesting Fund Prices_PK'

    -- You can't be late until you show up.

  • You have to setup EM to include indexes when scripting - has that been done?

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Thanks for the replies.

    I know you have to specifically script the indexes from EM, it's not that.

    I'm just wondering how the indexes could become divorced from the tables.

    Renaming the table (using sp_rename) doesn't do it.

    Dropping the table seems to remove the entry in sysindexes cleanly.

    @tosscrosby, if you had that script it'd be very handy for me ?

  • Something like this. Keep in mind, I use this is SQL 2000 and it will need to be tweaked for 2005/2008. Modify the like clause to suit your needs.

    select u.name as TbOwner, o.name as TbName , x.name as IxName, xc.name as IxColName,

    xk.keyno as KeyOrder

    from sysobjects o

    inner join sysindexes x

    on o.id = x.id

    inner join sysindexkeys xk

    on x.id = xk.id

    and x.indid = xk.indid

    inner join syscolumns xc

    on o.id = xc.id

    and xk.colid = xc.colid

    inner join sysusers u

    on u.uid = o.uid

    where o.xtype = 'U'

    and keys is not null

    and x.name like 'aaaa%'

    order by o.name, x.name, xk.keyno

    -- You can't be late until you show up.

  • while querying on sysindexes just try

    select object_name(id) from sysindexes where name like ''

    Silly but definitely makes sense

  • arr.nagaraj (3/18/2009)


    select object_name(id) from sysindexes where name like ''

    While this definitely will work, it won't provide the table name that the index is associated with. In an earlier post I offered that I'd bet the index by that name existed on a different table. My posted query will pinpoint exactly which one it is. 😉

    -- You can't be late until you show up.

  • Hi terry,

    Are you trying to find the name of the table associated with a particular index?

    As per BOL 'id' on sysindexes maps to table name, so it find it easily.

    Am I missing smthng? Please clarify.

    Regards,

    S.V.Nagaraj

  • I'll be darned. :blush: Learned something new today! I've used my script for so long, never tried to find a better (easier) way to do it. Thanks.

    -- You can't be late until you show up.

  • Thanks very much folks.

    Looks like I have a renegade "backup and rename" merchant somewhere in the building.

Viewing 10 posts - 1 through 9 (of 9 total)

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