create or alter proc SP_CreateIndex

as

begin

if exists(select * from sys.objects where name='execsql')

begin

drop table execsql;

end

create table execsql(id int identity(1,1),sqlstr varchar(1000),flag varchar(255));

--创建索引

insert into execsql(sqlstr,flag)

select 'create index IX_PK_'+a.FieldName+' on '+b.TableName+'('+a.FieldName+')' ,''from ResourceConfigurationDetail a

inner join ResourceConfiguration b

on a.FID=b.ID

where a.FieldType is not null;

--创建intime 索引

insert into execsql(sqlstr,flag)

select 'create index IX_PK_INTIME ON ' +a.tablename+'(INTIME)','' from ResourceConfiguration a;

print('创建索引脚本生成');

begin

declare @sql nvarchar(2000) ,@id int

declare cur_order cursor for --申明游标

select id,sqlstr from MultipleAnalysisFY.[dbo].[execsql] where flag<>'1' order by id

open cur_order --打开游标

fetch next from cur_order into @id,@sql

RAISERROR ('执行开始......', 10, 1) WITH NOWAIT

while @@FETCH_STATUS=0

begin

declare @info varchar(255)

set @info= @sql+'正在执行，本次id为'+convert(varchar(10),@id)+'当前时间:'+convert(varchar(100),getdate(),120);

RAISERROR (@info, 10, 1) WITH NOWAIT

begin try

set @sql= 'use MultipleAnalysisDataFY;'+@sql+';';

EXEC sp_executesql @sql;--执行脚本

declare @info1 varchar(255)

set @info1= @sql+'执行完成,当前时间:'+convert(varchar(100),getdate(),120);

update MultipleAnalysisFY.[dbo].[execsql] set flag='1' where id=@id

RAISERROR (@info1, 10, 1) WITH NOWAIT

end try

begin catch

declare @error varchar(255)

set @error='执行错误，错误信息id：'+convert(varchar(10),@id)+',错误信息:'+ERROR_MESSAGE();

RAISERROR (@error, 10, 1) WITH NOWAIT

RAISERROR (@sql, 11, 1) WITH NOWAIT

end catch

fetch next from cur_order into @id,@sql

end

close cur_order --关闭游标

DEALLOCATE cur_order--释放游标

print('任务处理完成');

drop table MultipleAnalysisFY.[dbo].[execsql];

end

end

--调用

exec SP_CreateIndex