Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table not showing PK - PK is in sysindexes ?


Table not showing PK - PK is in sysindexes ?

Author
Message
Joseph Fallon
Joseph Fallon
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 839
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'



id status first indid root minlen keycnt groupid dpages reserved used rowcnt rowmodctr reserved3 reserved4 xmaxlen maxirow OrigFillFactor StatVersion reserved2 FirstIAM impid lockflags pgmodctr keys name statblob maxlen rows
1548584605 2050 0x000000000000 3 0x000000000000 19 3 1 0 0 0 0 -954 0 0 36 42 90 0 0 0x000000000000 0 0 0 0x34013400020005000000000000000000010001000000000004000100000000003D013D0008001703000000000000000002000300000000000600030000000000AD01AD0008000000000000000000000000000B000000000200000B0000000100 aaaaaInvesting Fund Prices_PK NULL 8000 0



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.
tosscrosby
  tosscrosby
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2688 Visits: 5316
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.
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4463 Visits: 9833
You have to setup EM to include indexes when scripting - has that been done?

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

Joseph Fallon
Joseph Fallon
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 839
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 ?
tosscrosby
  tosscrosby
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2688 Visits: 5316
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.
arr.nagaraj
arr.nagaraj
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 1588
while querying on sysindexes just try

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

Silly but definitely makes sense

Regards,
Raj

http://Strictlysql.blogspot.com
tosscrosby
  tosscrosby
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2688 Visits: 5316
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.
arr.nagaraj
arr.nagaraj
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 1588
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

Regards,
Raj

http://Strictlysql.blogspot.com
tosscrosby
  tosscrosby
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2688 Visits: 5316
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.
Joseph Fallon
Joseph Fallon
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 839
Thanks very much folks.

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search