Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Table not showing PK - PK is in sysindexes ? Expand / Collapse
Author
Message
Posted Friday, March 13, 2009 9:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 23, 2014 7:35 AM
Points: 162, Visits: 752
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.
Post #675337
Posted Friday, March 13, 2009 3:24 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 7:31 AM
Points: 2,666, Visits: 5,316
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.
Post #675641
Posted Friday, March 13, 2009 3:44 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, October 2, 2014 12:09 PM
Points: 4,358, Visits: 9,538
You have to setup EM to include indexes when scripting - has that been done?

Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #675651
Posted Monday, March 16, 2009 3:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 23, 2014 7:35 AM
Points: 162, Visits: 752
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 ?

Post #676324
Posted Monday, March 16, 2009 6:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 7:31 AM
Points: 2,666, Visits: 5,316
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.
Post #676421
Posted Wednesday, March 18, 2009 4:44 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 12:44 AM
Points: 490, Visits: 1,341
while querying on sysindexes just try

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

Silly but definitely makes sense



Regards,
Raj

Strictlysql.blogspot.com
Post #678258
Posted Wednesday, March 18, 2009 6:20 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 7:31 AM
Points: 2,666, Visits: 5,316
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.
Post #678336
Posted Wednesday, March 18, 2009 7:33 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 12:44 AM
Points: 490, Visits: 1,341
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

Strictlysql.blogspot.com
Post #678436
Posted Wednesday, March 18, 2009 7:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 7:31 AM
Points: 2,666, Visits: 5,316
I'll be darned. 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.
Post #678448
Posted Wednesday, March 18, 2009 10:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, October 23, 2014 7:35 AM
Points: 162, Visits: 752
Thanks very much folks.

Looks like I have a renegade "backup and rename" merchant somewhere in the building.
Post #678672
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse