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 |
|
|
|