I am looking for some help to drop secondary indexes and referential integrities on a table .I have to implement this for several tables , and I need to use this in my scritp for future purpose also.
I saw DBC.IndicesV having databasename tablename , indexType and IndexNumber as well.
I am tring to export the databasename, tablename and column involved in the Secondary Index
select trim(databasename),trim(tablename) ,trim(ColumnName)
where databasename ='$DatabaseName'
and tablename ='$TableName'
and IndexType ='S';
DROP INDEX ($columnName) ON DatabaseName.TableName;
Dropping single column is working, but Multi column Indexes is getting difficult to drop with the above SQL.
Is there any DBC table that will hold the Columns like (col1,col2) to drop the multi column secondary Indexes?
or DO I need to GO with the IndexNumer and ColumnPosition ( this logic is going to be a very complecate I guess)..Please advice.
Why don't you want to write recursive query to make comma list of the fields you need? Or you can create NAMED indexes to specify only index name and table name:
drop index MyIndex on MyTable;