Drop Index

[morsi]

Hi,
I would like to drop index in a databse different from the current db. In creation the syntax "create index index_name on dbname_db..table_name(..)" is ok but "drop index dbname_db..table_name.index_name" don't work ? Besides, deleting line from sysindexes isn't possible in my default configuration.
Thanks.

 

[sybaseguru]

Morsi,

My advice is that first make sure that you do not mess around with system tables otherwise you are going to have trouble.
Since you can create an object in another database (i.e. your user in the current database has "creation rights" in another database (most probably a dbo), I suggest that you go to that database i.e.

use <database>
go
<drop index <table_name>.<index_name>
go

This should work otherwise ask the dba to drop it for you. Other alternative is to copy the table into another table with select * into <table_name>_prev from <table_name>, drop the old table (which will drop the index and triggers as well) and rename the prev table to the actual table name, create the index and triggers and do a sp_recompile <table_name>.
The easiest option would be to ask the dba to drop the index for you or if you can go to that database and drop the index yourself.

Good luck

 

[morsi]

The drop of index that i like to do is in ps dynamically with &quot;exec&quot;. I can't use &quot;use namedb_db&quot; in ps !
The second solution is hard to implement dynamically in a ps but if no other solution is possible i will try this.
A+

 

[sybaseguru]

Why do you need to do DDL type work dynamically? I believe you have sql advantage access, can you use that to drop the index?

 

[morsi]

I have some index descriptions in a table : INDEX_NAME, TABLE_NAME, COLUMNS (as varchar eg. 'COL1, COL3'), IND_UNIQUE and IND_CLUSTEED. I would like fetching this table in a ps to create indexes with exec command. I must deleting indexes before creation but tables are located in database different from db where i must create and execute the ps.