欢迎进入windows社区论坛，与300万技术人员互动交流 >>进入 commit transaction end try begin catch print '===== can''t switch ' + @foreignkey + ' to cascade, - ' + cast（error_number（） as varchar） + ' - ' + error_message（）； rollback tran
欢迎进入windows社区论坛，与300万技术人员互动交流 >>进入
commit transaction
end try
begin catch
print '=====> can''t switch ' + @foreignkey + ' to cascade, - ' +
cast（error_number（） as varchar） + ' - ' + error_message（）；
rollback transaction
end catch;
fetch next from databasetablerelationships into @foreignkey, @columnname, @referencetablename, @referencecolumnname;
end;
close databasetablerelationships;
deallocate databasetablerelationships;
end
print '';
print '';
fetch next from databasetables0
into @tableowner,@tablename;
end
close databasetables0;
deallocate databasetables0;
print（'loop though each table and delete all data from the table'）
declare databasetables1 cursor for
select schema_name（t.schema_id） as schema_name, t.name as table_name
from sys.tables as t;
open databasetables1;
fetch next from databasetables1
into @tableowner,@tablename;
while @@fetch_status = 0
begin
if （not exists（select top 1 1 from @ignoretables where tablename = @tablename））
begin
print '['+@tableowner+'].[' + @tablename + ']';
print '=====> deleting data from ['+@tableowner+'].[' + @tablename + ']';
begin try
exec（'
delete from ['+@tableowner+'].[' + @tablename + ']
dbcc checkident （[' + @tablename + '], reseed, 0）
'）；
end try
begin catch
print '=====> can''t from ['+@tableowner+'].[' + @tablename + '], - ' +
cast（error_number（） as varchar） + ' - ' + error_message（）；
end catch;
end
print '';
print '';
fetch next from databasetables1
into @tableowner,@tablename;
end
close databasetables1;
deallocate databasetables1;
print（'loop through all tables and switch all constraints to have a delete rule they had at the beggining of the task'）
declare databasetables2 cursor for
select schema_name（t.schema_id） as schema_name, t.name as table_name
from sys.tables as t;
open databasetables2;
fetch next from databasetables2
into @tableowner,@tablename;
while @@fetch_status = 0
begin
if （not exists（select top 1 1 from @ignoretables where tablename = @tablename））
begin
print '['+@tableowner+'].[' + @tablename + ']';
declare databasetablerelationships cursor for
select foreignkey, columnname, referencetablename, referencecolumnname, deleterule
from @allrelationships
where tablename = @tablename
open databasetablerelationships;
fetch next from databasetablerelationships into @foreignkey, @columnname, @referencetablename, @referencecolumnname, @deleterule;
if @@fetch_status 0
print '=====> no relationships' ;
while @@fetch_status = 0
begin
declare @switchbackto varchar（50） =
case
when @deleterule = 'no_action' then 'no action'
when @deleterule = 'cascade' then 'cascade'
when @deleterule = 'set_null' then 'set null'
when @deleterule = 'set_default' then 'set default'
end
[1] [2] [3]