在实际应用中，，当我们准备把一个项目移交至客户手中使用时，我们需要把库中所有表先前的测试数据清空，以给客户一个干净的数据库，如果涉及的表很多，要一一的清空，不仅花费时间，还容易出错以及漏删，在这儿我提供了一个方法，可快捷有效的清空指定数据库所有表的数据。仅供参考，欢迎交流不同意见。
--remove all data from a database
set nocount on
--tables to ignore
declare @ignoretables
table (tablename varchar(512))
insert into @ignoretables (tablename) values ('sysdiagrams')
declare @allrelationships
table (foreignkey varchar(512)
,tablename varchar(512)
,columnname varchar(512)
,referencetablename varchar(512)
,referencecolumnname varchar(512)
,deleterule varchar(512))
insert into @allrelationships
select f.name as foreignkey,
object_name(f.parent_object_id) as tablename,
col_name(fc.parent_object_id,
fc.parent_column_id) as columnname,
object_name (f.referenced_object_id) as referencetablename,
col_name(fc.referenced_object_id,
fc.referenced_column_id) as referencecolumnname,
delete_referential_action_desc as deleterule
from sys.foreign_keys as f
inner join sys.foreign_key_columns as fc
on f.object_id = fc.constraint_object_id
declare @tableowner varchar(512)
declare @tablename varchar(512)
declare @foreignkey varchar(512)
declare @columnname varchar(512)
declare @referencetablename varchar(512)
declare @referencecolumnname varchar(512)
declare @deleterule varchar(512)
print('loop through all tables and switch all constraints to have a delete rule of cascade')
declare databasetables0
cursor for
select schema_name(t.schema_id) as schema_name, t.name as table_name
from sys.tables as t;
open databasetables0;
fetch next from databasetables0
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
from @allrelationships
where tablename = @tablename
open databasetablerelationships;
fetch next from databasetablerelationships into @foreignkey, @columnname, @referencetablename, @referencecolumnname;
if @@fetch_status 0
print '=====> no relationships' ;
while @@fetch_status = 0
begin
print '=====> switching delete rule on ' + @foreignkey + ' to cascade';
begin transaction
begin try
exec('
alter table ['+@tableowner+'].[' + @tablename + ']
drop constraint '+@foreignkey+';
alter table ['+@tableowner+'].[' + @tablename + '] add constraint
'+@foreignkey+' foreign key
(
'+@columnname+'
) references '+@referencetablename+'
(
'+@referencecolumnname+'
) on delete cascade;
');
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
print '=====> switching delete rule on ' + @foreignkey + ' to ' + @switchbackto;
begin transaction
begin try
exec('
alter table ['+@tableowner+'].[' + @tablename + ']
drop constraint '+@foreignkey+';
alter table ['+@tableowner+'].[' + @tablename + '] add constraint
'+@foreignkey+' foreign key
(
'+@columnname+'
) references '+@referencetablename+'
(
'+@referencecolumnname+'
) on delete '+@switchbackto+'
');
commit transaction
end try
begin catch
print '=====> can''t change '+@foreignkey + ' back to '+ @switchbackto +', - ' +
cast(error_number() as varchar) + ' - ' + error_message();
rollback transaction
end catch;
fetch next from databasetablerelationships
into @foreignkey, @columnname, @referencetablename, @referencecolumnname, @deleterule;
end;
close databasetablerelationships;
deallocate databasetablerelationships;
end
print '';
print '';
fetch next from databasetables2
into @tableowner,@tablename;
end
close databasetables2;
deallocate databasetables2;