DROP Index problem

Question

Post reply

DROP Index problem

Purclot Avignon

SSC Enthusiast

Points: 193
More actions
December 2, 2010 at 5:00 am

#229705

hi there,
i'm playing with SQL Server 2008 on my XP-Workstation.
I created a table and some indexes on it - so I'm the dbo for this table, right?
I can see the created indexes (sp_helpindex testen).
Wenn I try to: Drop Index test1 on testen.myfield
I get an error saying: cannont find testen.myfield or I don't have permission (both are false, I suppose..).
What's wrong?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Please post the create statement for both the table and indexes and the drop statement you are trying to execute.

Purclot Avignon SSC Enthusiast Points: 193 More actions · Answer 2

create table testen

(iid int not null, strasse varchar(50), plz varchar(10))

insert testen

values (3,'gutes 12','1123')

create nonclustered index strasse on testen(strasse)

sp_helpindex testen

ALTER INDEX strasse ON testen.strasse

REBUILD; => here I get an error saying: 'Msg 1088, Level 16, State 9, Line 1

Cannot find the object "testen.strasse" because it does not exist or you do not have permissions.'

Lowell SSC Guru Points: 323518 More actions · Answer 3

it's the tablename: the table is not "testen.strasse", it is "testen" or "dbo.testen"

the code is looking for a schema testen and a table strasse, which does not exist.

ALTER INDEX strasse ON testen

REBUILD;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Purclot Avignon SSC Enthusiast Points: 193 More actions · Answer 4

upps..

thank you for your help, it works!

I was just thinking:

DROP Index table_name.index_name

also must be:

ALTER INDEX index_name ON table_name.index_name REBUILD;

but I was wrong...

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

I'm not sure you guys are talking about the same thing :

Rebuild is used to make a copy of the index and drop the old one (which 100% defrags it in the process).

Drop means delete.

Philipp Post Default port Points: 1401 More actions · Answer 6

karl-heinz (12/2/2010)
upps..
thank you for your help, it works!
I was just thinking:
DROP Index table_name.index_name
also must be:
ALTER INDEX index_name ON table_name.index_name REBUILD;
but I was wrong...

In SQL Server index names must be unique by table, but not by schema (as it is the case in DB2 for example). This means the same index name can exist in different tables and therefore you need to tell the system in which table you wish to drop it.

brgds

Philipp Post