JohnMac328
asked on
SQL Drop index from table without knowing the name of the index
We have tables out in the field and we have to drop any indexes that we are not aware of. How do you drop an index from a table without knowing the name.
Thanks
John
Thanks
John
Some RDBMS have system views to list indices like sys.indexes in SQL Server.
ASKER
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
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
ASKER
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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);
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);
ASKER
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;
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.
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.