------------------------------------------------
-- 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