Thursday, August 09, 2007

list of table indexes

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

No comments: