SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Index is not dropped
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vijays3
Constraint Violating Yak Guru

India
338 Posts

Posted - 05/23/2010 :  07:09:09  Show Profile  Reply with Quote
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
8760 Posts

Posted - 05/23/2010 :  07:15:21  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

vijays3
Constraint Violating Yak Guru

India
338 Posts

Posted - 05/23/2010 :  10:21:56  Show Profile  Reply with Quote
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.

Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 05/23/2010 :  11:40:43  Show Profile  Reply with Quote
Have you refreshed indexes?
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
789 Posts

Posted - 05/23/2010 :  12:26:33  Show Profile  Reply with Quote
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.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 05/23/2010 :  14:01:55  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 05/23/2010 :  15:58:14  Show Profile  Reply with Quote
Yah that was infact Eagle Eye
Go to Top of Page

kashyapsid
Yak Posting Veteran

India
78 Posts

Posted - 05/24/2010 :  03:30:38  Show Profile  Visit kashyapsid's Homepage  Reply with Quote
wt is a eagle eye

KaShYaP
Go to Top of Page

vijays3
Constraint Violating Yak Guru

India
338 Posts

Posted - 05/24/2010 :  06:26:19  Show Profile  Reply with Quote

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.

Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 05/24/2010 :  13:17:20  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
789 Posts

Posted - 05/24/2010 :  22:14:26  Show Profile  Reply with Quote
Thanks everyone - but really, all I did was put on my glasses :)
Go to Top of Page

zstarsales04
Starting Member

20 Posts

Posted - 05/25/2010 :  02:19:04  Show Profile  Visit zstarsales04's Homepage  Reply with Quote
spam removed
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000