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