| Author |
Topic |
|
vijays3
Constraint Violating Yak Guru
354 Posts |
 Posted - 2010-05-23 : 07:09:09
|
| Hi All,My table sysdba.usercalendar having a following information regarding index USERCAL_PRIMARY nonclustered, unique located on PRIMARY USERID,CALUSERIDI 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
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-05-23 : 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
354 Posts |
Posted - 2010-05-23 : 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
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2010-05-23 : 11:40:43
|
| Have you refreshed indexes? |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-05-23 : 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
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-05-23 : 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
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2010-05-23 : 15:58:14
|
Yah that was infact Eagle Eye |
|
|
|
kashyapsid
Yak Posting Veteran
78 Posts |
Posted - 2010-05-24 : 03:30:38
|
| wt is a eagle eyeKaShYaP |
|
|
|
vijays3
Constraint Violating Yak Guru
354 Posts |
Posted - 2010-05-24 : 06:26:19
|
Thanks for pointing me out ... I was not able to see it ...Thanks for Eagle EYequote:
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
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-05-24 : 13:17:20
|
quote:
Originally posted by kashyapsid
wt is a eagle eyeKaShYaP
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
819 Posts |
Posted - 2010-05-24 : 22:14:26
|
| Thanks everyone - but really, all I did was put on my glasses :) |
|
|
|
zstarsales04
Starting Member
20 Posts |
Posted - 2010-05-25 : 02:19:04
|
| spam removed |
|
|
|
|
Index is not dropped