| Author |
 Topic  |
|
|
vijays3
Constraint Violating Yak Guru
India
311 Posts |
 Posted - 05/23/2010 : 07:09:09
|
Hi All,
My table sysdba.usercalendar having a following information regarding index
USERCAL_PRIMARY nonclustered, unique located on PRIMARY USERID,CALUSERID
I am trying to drop this index using below command
IF EXISTS (SELECT * FROM dbo.sysindexes WHERE name = 'USERCAL_PRIMARY'
AND id = object_id('[SYSDBA].[TICKET]'))
DROP INDEX USERCAL_PRIMARY ON [SYSDBA].[USERCALENDAR]
The result comes with completed successfully,although again i check the indexes on this table by using this command sp_helpindex 'SYSDBA.USERCALENDAR'. It shows index on the column it is not dropped
Kindly somone put their comment on this |
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8529 Posts |
Posted - 05/23/2010 : 07:15:21
|
maybe the name is a bit different.
What gives this?:
SELECT * FROM dbo.sysindexes WHERE name like '%USERCAL%PRIMARY%'
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
vijays3
Constraint Violating Yak Guru
India
311 Posts |
Posted - 05/23/2010 : 10:21:56
|
No I checked all possible thing before and after dropping a index ..It is not affecting at all.This USERCAL_PRIMARY index is created on [USERID,CALUSERID] as i mentioned i have to drop this index and need to create new index USECALENDAR_USERID On USERID column only...Any suggestion on this.
quote:
Originally posted by webfred
maybe the name is a bit different.
What gives this?:
SELECT * FROM dbo.sysindexes WHERE name like '%USERCAL%PRIMARY%'
No, you're never too old to Yak'n'Roll if you're too young to die.
|
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 05/23/2010 : 11:40:43
|
| Have you refreshed indexes? |
|
|
|
jeffw8713
Aged Yak Warrior
USA
699 Posts |
Posted - 05/23/2010 : 12:26:33
|
Your code is checking for an index on the table SYSDBA.TICKET. If that index exists, it drops the index on the table SYSDBA.USERCALENDAR.
So, the code works - but your index is not dropped because you are not checking for the correct table. Try running the exists code separately and verify that it returns a value. |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8529 Posts |
Posted - 05/23/2010 : 14:01:55
|
quote:
Originally posted by jeffw8713
Your code is checking for an index on the table SYSDBA.TICKET. If that index exists, it drops the index on the table SYSDBA.USERCALENDAR.
So, the code works - but your index is not dropped because you are not checking for the correct table. Try running the exists code separately and verify that it returns a value.
Eagle Eye 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 05/23/2010 : 15:58:14
|
Yah that was infact Eagle Eye |
|
|
|
kashyapsid
Yak Posting Veteran
India
78 Posts |
Posted - 05/24/2010 : 03:30:38
|
wt is a eagle eye
KaShYaP |
|
|
|
vijays3
Constraint Violating Yak Guru
India
311 Posts |
Posted - 05/24/2010 : 06:26:19
|
Thanks for pointing me out ... I was not able to see it ...Thanks for
Eagle EYe
quote:
Originally posted by jeffw8713
Your code is checking for an index on the table SYSDBA.TICKET. If that index exists, it drops the index on the table SYSDBA.USERCALENDAR.
So, the code works - but your index is not dropped because you are not checking for the correct table. Try running the exists code separately and verify that it returns a value.
|
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8529 Posts |
Posted - 05/24/2010 : 13:17:20
|
quote:
Originally posted by kashyapsid
wt is a eagle eye
KaShYaP
An eagle is a bird with very good eyes.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
jeffw8713
Aged Yak Warrior
USA
699 Posts |
Posted - 05/24/2010 : 22:14:26
|
| Thanks everyone - but really, all I did was put on my glasses :) |
|
|
|
zstarsales04
Starting Member
20 Posts |
Posted - 05/25/2010 : 02:19:04
|
| spam removed |
|
|
| |
Topic |
|
Index is not dropped
