print('Creating indexes on GIM tables by CREATE_AUDIT_KEY, UPDATE_AUDIT_KEY required for export data ...');
declare missingIndexCur cursor for
	with index_info as (
		select
		   t.name                                                                               as TABLE_NAME
		  ,i.name                                                                               as INDEX_NAME
		  ,i.is_unique                                                                          as IS_UNIQUE
		  ,i.is_primary_key                                                                     as IS_PRIMARY_KEY
		  ,(case when i.index_id = 1 then 1 else 0 end)                                         as IS_CLUSTERED
		  ,cl.name                                                                              as COLUMN_NAME
		  ,(case when c.key_ordinal > 0 then c.key_ordinal else 1000 * c.partition_ordinal end) as KEY_ORDINAL
		  ,c.is_included_column                                                                 as IS_INCLUDED_COLUMN
		  ,c.is_descending_key                                                                  as IS_DESCENDING_KEY
		from sys.tables        t
		join sys.indexes       i on i.object_id =  t.object_id
		join sys.index_columns c on i.object_id =  c.object_id and c.index_id   = i.index_id
		join sys.columns      cl on i.object_id = cl.object_id and cl.column_id = c.column_id
		where (c.key_ordinal > 0 or c.partition_ordinal > 0)
	),
	table_info as (
		select 
		   t.name TABLE_NAME
		  ,c.name COLUMN_NAME
		from sys.tables        t
		join sys.columns       c on t.object_id = c.object_id and t.type = 'U'
		where 
            patindex('ADMIN_%',         t.name) = 0
        and patindex('CTL_%',           t.name) = 0
        and patindex('GIDB%',           t.name) = 0
        and patindex('G_CALL',          t.name) = 0
        and patindex('G_IR',            t.name) = 0
        and patindex('G_IS_LINK',       t.name) = 0
        and patindex('STG_%',           t.name) = 0
        and patindex('TMP_%',           t.name) = 0
        and patindex('T_VIEW_%',        t.name) = 0
        and patindex('dual',            t.name) = 0
        and patindex('AG2_%',           t.name) = 0
        and patindex('AGR_%',           t.name) = 0
        and patindex('AGT_%',           t.name) = 0
        and patindex('GI2_%',           t.name) = 0
        and patindex('%GI2_',           t.name) = 0
        and patindex('%_GI2',           t.name) = 0
        and patindex('PENDING_AGR',     t.name) = 0
        and patindex('RELATIVE_RANGE',  t.name) = 0
        and patindex('TODAY',           t.name) = 0
		and c.name in('CREATE_AUDIT_KEY', 'UPDATE_AUDIT_KEY')
	)
	select t.* 
	from table_info      t
	left join index_info i on i.KEY_ORDINAL = 1 and t.TABLE_NAME = i.TABLE_NAME and t.COLUMN_NAME = i.COLUMN_NAME
	where i.TABLE_NAME is null;
declare @tableName       varchar(32);
declare @columnName      varchar(32);
declare @sqlStr          nvarchar(256);

open missingIndexCur;
fetch next from missingIndexCur into @tableName, @columnName;
while @@fetch_status = 0
begin
	if @columnName = 'CREATE_AUDIT_KEY' set @sqlStr = 'create index I_' + @tableName + '_CAK on ' + @tableName + '(CREATE_AUDIT_KEY asc)';
	else                                set @sqlStr = 'create index I_' + @tableName + '_UAK on ' + @tableName + '(UPDATE_AUDIT_KEY asc)';
	print('Running : '+ @sqlStr);
	execute sp_executesql @sqlStr;
	fetch next from missingIndexCur into @tableName, @columnName;
end
close      missingIndexCur;
deallocate missingIndexCur;
print('Done');
