March 13, 2009 at 9:35 am
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.
March 13, 2009 at 3:24 pm
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.
March 13, 2009 at 3:44 pm
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
March 16, 2009 at 3:18 am
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 ?
March 16, 2009 at 6:28 am
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.
March 18, 2009 at 4:44 am
while querying on sysindexes just try
select object_name(id) from sysindexes where name like ''
Silly but definitely makes sense
Regards,
Raj
March 18, 2009 at 6:20 am
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.
March 18, 2009 at 7:33 am
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
March 18, 2009 at 7:38 am
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.
March 18, 2009 at 10:06 am
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