SELECT name FROM sysindexes WHERE id = OBJECT_ID(N'MyTable') AND indid > 0 AND indid < 255 AND INDEXPROPERTY(id, name, 'IsStatistics') = 0 AND name <> (SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'MyTable')>I have this script which removes all indexes from a table. However, as primary keys should be handled differently, I need to adjust this script so it will not grab a primary key. Is that possible?
>DECLARE tableIndexes CURSOR FOR >SELECT name FROM sysindexes >WHERE id = OBJECT_ID(N'MyTable') AND > indid > 0 AND indid < 255 AND > INDEXPROPERTY(id, name, 'IsStatistics') = 0 >ORDER BY indid DESC > >OPEN tableIndexes >FETCH NEXT FROM tableIndexes INTO @indexName >WHILE @@fetch_status = 0 >BEGIN > SET @dropIndexSql = N'DROP INDEX MyTable.' + @indexName > EXEC sp_executesql @dropIndexSql > FETCH NEXT FROM tableIndexes INTO @indexName >END >