I was need to drop all indexes for specific table excluding primary key indexes.
Following example demonstrates how to get list of indexes.
USE mydb;
SELECT indexes.name, * FROM sysindexes indexes
INNER JOIN sysobjects objects ON indexes.id = objects.id
WHERE indexes.indid>0 AND objects.name='mytable'
and indexes.name not in
(select constraint_name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name = 'mytable' and constraint_type = 'primary key')
if table doesn't have clustered index, then result set of sysindexes will contain heap with the field indid=0.. Because it is not real index, and I cannot drop it, I am filtering it out by the indid>0
Lately I found that table may contain statistic indexes, which cannot be dropped as well directly with DROP INDEX, but through "drop statistics" statement
To filter them out I added following part to WHERE:
and INDEXPROPERTY(indexes.id, indexes.name, 'IsStatistics')=0
and INDEXPROPERTY(indexes.id, indexes.name, 'IsHypothetical') = 0
and INDEXPROPERTY(indexes.id, indexes.name, 'IsAutoStatistics') = 0
Thursday, August 09, 2007
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment