SQL 游标、循环使用

–使用游标不需要一个一个手动操作

IF EXISTS(SELECT 1 FROM sys.tables where name ='temp_table')
DROP TABLE temp_table  
go
create table temp_table 
(
	tablename nvarchar(200),
	ftabn  nvarchar(200),
	sx  int
)
insert into temp_table
(tablename,ftabn , sx)
select 'BsPatient' as tabname ,'', 1 as sx 

declare @i int,
@sql nvarchar(1000),
@tablename nvarchar(200)
set @i=1
while  @i<50
begin 
	insert into temp_table
	SELECT   c.name as tabname ,d.name as ftabn , @i+1 as sx
	FROM sysobjects a 
	join sysforeignkeys b on a.id=b.constid   
	join sysobjects c on a.parent_obj=c.id  
	join   sysobjects d on b.rkeyid = d.id 
	where a.xtype='f' AND c.xtype='U'  
	and  d.name in( select tablename from temp_table where  sx = @i  )
	set @i=@i+1  
end;

--关闭触发器
IF EXISTS(SELECT 1 FROM TEMPDB..SYSOBJECTS WHERE ID=OBJECT_ID('TEMPDB..#temp'))
DROP TABLE #temp 
go
begin
select a.name trname,b.name tablename ,c.is_disabled into #temp
from   sysobjects  a
join sysobjects b on a.parent_obj=b.id
join sys.triggers c on a.id = c.object_id 
where a.xtype='TR' 
and b.name in (select tablename from temp_table)
and c.is_disabled=0;
--select a.name trname,b.name tablename into #temp  from   sysobjects  a
--join sysobjects b on a.parent_obj=b.id
--where a.xtype='TR'  and exists(select 1 from sys.triggers 
--where object_id=a.id and sys.triggers.is_disabled=0)
--and b.name in (select tablename from temp_table);
declare  @sql1 nvarchar(1000);
declare cs_db1 cursor for
	select 'alter table '+tablename+' disable trigger '+ trname   from   #temp	 
	open cs_db1
	fetch cs_db1 into @sql1
	while @@fetch_status=0
	begin 
		exec sp_executesql @sql1
		--print(@sql1)
		fetch cs_db1 into @sql1
	end
close cs_db1
deallocate cs_db1
end

--declare	
--	@cnt int;
--begin
--    select @cnt=count(1) from temp_table;    
--	while @cnt > 0 
--	begin
--	    select a.name trname,b.name tablename into #temp  from   sysobjects  a
--		join sysobjects b on a.parent_obj=b.id
--		where a.xtype='TR'  and exists(select 1 from sys.triggers where object_id=a.id and sys.triggers.is_disabled=0)
--		and b.name in (select tablename from temp_table);
		
--		select @sql='alter table '+tablename+' disable trigger '+ trname   from   #temp;			 
--		exec sp_executesql @sql;
--		print @sql;
--		set @cnt =@cnt-1;		
--	end;
--end
update InHosInfo set MotherHospId=null
--删除数据
--CREATE proc UspAoutImpDelete_yw1
--as
--begin
--declare  
--	@sql2 nvarchar(1000);
--declare cs_db2 cursor for
--	select  'delete  '+tablename from  temp_table order by sx desc

--	open cs_db2
--	fetch  cs_db2 into @sql2
--	while @@fetch_status=0
--	begin 
--		exec sp_executesql @sql2
--		--print(@sql2)
--		fetch cs_db2 into @sql2
--	end 
--close cs_db2
--deallocate cs_db2
--end
begin
declare 
	@sql2 nvarchar(1000),
	@i2 int
	set @i2=1
	while @i2<5
		begin
			exec UspAoutImpDelete_yw1
			--print 'exec UspAoutImpDelete_yw1'
			set @i2=@i2+1
		end
end


--开启触发器
begin
declare  @sql3 nvarchar(1000);
declare cs_db3 cursor for
	select 'alter table '+tablename+' enable trigger '+trname from  #temp
	open cs_db3
	fetch cs_db3 into @sql3
	while @@fetch_status=0
	begin 
		exec sp_executesql @sql3
		--print(@sql3)
		fetch cs_db3 into @sql3
	end
close cs_db3
deallocate cs_db3
end