------------------------------------------------ -- this script finds all the indexes that start -- with IX_ZCJ_ and deletes each one as it -- loops through the 280+ indexes -- updated: 2014-10-21 ------------------------------------------------ begin set nocount on -------------------------------------------------------- -- get a list of ceo juice indexes -------------------------------------------------------- declare @IndexList table ( IndexName varchar(255), TableName varchar(255) ) declare @CurIndexName varchar(255) declare @CurTableName varchar(255) declare @Cmd varchar(1000) insert @IndexList select i.name as IndexName, t.name as TableName from sys.indexes i inner join sys.tables t on t.object_id = i.object_id where i.name like 'IX_ZCJ_%' --CEO_ to capture old naming convention or i.name like 'CEO_%' or i.name like 'IX_CEO_%' -- select * from @IndexList -------------------------------------------------------- -- loop through and drop custom indexes -------------------------------------------------------- declare IndexList cursor for select IndexName, TableName from @IndexList open IndexList fetch next from IndexList into @CurIndexName, @CurTableName while @@fetch_status = 0 begin set @Cmd = 'drop index ' + '[' + @CurIndexName + ']' + ' on ' + '[' + @CurTableName + ']' exec (@Cmd ) --print (@Cmd) fetch next from IndexList into @CurIndexName, @CurTableName end close IndexList deallocate IndexList end