SQL Drop index from table without knowing the name of the index

Some RDBMS have system views to list indices like sys.indexes in SQL Server.

We have to send out code to do a update, two tables may have additional indexes and we need to drop them before we run the code.  Isn't there a generic drop statement to get rid of any indexes we do not know of in the two tables?

Hi,

As Ste5an mentions then querying the index table in the system catalog tables of your RDBMS will list all indexes associated with the  table(s) that you are looking at.

For Db2 LUW it is
syscat.indexes
For Db2 z/OS
sysibm.sysindexes
For Oracle
sys.all_indexes

Note that dropping indexes already on a table in a database can have drastic performance implications on systems running on those tables involved in any update procedure.  
Additional indexes are often applied to tables by a DBA to solve performance issues.
Query-ing the catalog tables and generate backup of tables/views/indexes and any other objects that is to be updated should be done and kept in a ddl file.
Such backup procedure is  in my opinion mandatory in any "automatic" update procedure of an application for a DBA to be able to rollback any updates if necessary.

Regards,
   Tomas Helgi

So then we would have to contact each site, all 55 and do a remote to run the query to see if the two tables have any additional indexes we dont know about.  Kind of defeats the whole purpose of sending out code to do the update.  If I am missing anything please let me know, dealing with hospitals.

It is a distributed system and nobody is supposed to make any changes but we are finding that somehow there are indexes we are not aware of and it is stopping the update code from running.

but we are finding that somehow there are indexes we are not aware of and it is stopping the update code from running.                                  

Sorry, but then dropping those indexes "just" to get your code working seems so wrong, in so many ways!
This can't be the right/proper way!

Could you please clarify two things?
1) What RDBMS are you using?
2) What index can stop the update from running? Maybe some unique one but in such case it should be prohibited by the license agreement.

You should maybe also check all the constraints, possible extra tables and columns, new views, stored procedures etc. etc.

Netminder came close to what I needed - here is the final solution - Clears all indexes on named table but does not clear index if it is a primary key.  We are not rebuilding the indexes because they are not supposed to be there in the first place.

DECLARE myCur CURSOR FOR SELECT si.name, si.type FROM sys.indexes si where si.object_id = OBJECT_ID(@tablename) AND si.is_primary_key = 0;
SET @schemaName = QUOTENAME('yourschema'); SET @tableName = QUOTENAME('yourtable');
OPEN myCur
FETCH NEXT FROM myCur INTO @iName, @iType

SELECT @SQL2Run = '';

WHILE @@FETCH_STATUS = 0
      BEGIN
            SELECT @ExistsSQL = 'IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''' + @schemaName + '.' + @tableName + ''') AND name = N''' + @iName + ''')'  
            SELECT @DropSQL =  ' DROP INDEX [' + @iName  + '] ON ' + @schemaName + '.' + @tableName + CASE WHEN @iType IN (1,2) THEN ' WITH ( ONLINE = OFF )' ELSE '' END
            SELECT @SQL2Run = @SQL2Run + @ExistsSQL + @DropSQL + CHAR(10) + CHAR(13)
            FETCH NEXT FROM myCur INTO @iName, @iType
      END

CLOSE myCur
DEALLOCATE myCur

--PRINT @SQL2Run
EXEC(@SQL2Run);

Thanks slightwv (䄆 Netminder)

No cursor required.

DECLARE @TableName NVARCHAR(MAX) = N'schemaName.tableName;
DECLARE @Batch NVARCHAR(MAX) =
(
    SELECT 'DROP INDEX ' + QUOTENAME(I.name) + ' ON ' + QUOTENAME(S.name) + '.' + QUOTENAME(T.name) + ';' + CHAR(13)
    FROM sys.schemas S
        INNER JOIN sys.tables T
            ON T.name = S.name
        INNER JOIN sys.indexes I
            ON I.object_id = T.object_id
    WHERE I.is_primary_key = 0
          AND I.object_id = OBJECT_ID(@TableName)
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)');

PRINT @Batch;
--EXECUTE sys.sp_sqlexecute @Batch;

Select allOpen in new window

Always use QUOTENAME() for such scripts.

IMO, the fact whether cursor is used or not is irrelevant here. It does not affect the functionality or performance.

Neither solution does what was requested in the question: "we have to drop any indexes that we are not aware of".

To work correctly the query should contain a list of indexes "that we are aware of" otherwise it cannot do the job requested. But such list was reduced to PKs only.

OTOH, if the query is OK now then there is no need to improve it.