保存數據庫中其他對象不變，刪除數據庫中所有數據的實現方法
2024-07-21 02:07:58
供稿：網友
原帖內容：
怎樣把數據庫中所有數據刪除，然后把所有的自動增量復位？
表太多，無法手工完成。
http://community.csdn.net/expert/topic/3094/3094555.xml?temp=.2920954
/*
--原本打算這樣
--先禁用所有外鍵約束
exec sp_msforeachtable "alter table ? nocheck constraint all"
--然后刪除數據
exec sp_msforeachtable "truncate table ?"
--再啟用所有外鍵約束
exec sp_msforeachtable "alter table ? check constraint all"
--但是禁用了以后，truncate table 不行，會提示沖突
*/
--現在我的想法是（語句待優化）：
--第一部分，生成建立外鍵的語句保存到#tmp
declare @name varchar(200),@tmp1 varchar(500),@tmp2 varchar(500)
create table #tmp
(
string varchar(8000)
)
select 表名稱=object_name(b.fkeyid)
,外鍵名稱=a.name
,引用的列名=(select name from syscolumns where colid=b.fkey and id=b.fkeyid)
,引用的表名=object_name(b.rkeyid)
,已引用的列名=(select name from syscolumns where colid=b.rkey and id=b.rkeyid)
into #t from sysobjects a
join sysforeignkeys b on a.id=b.constid
join sysobjects c on a.parent_obj=c.id
where a.xtype='f' and c.xtype='u'
declare cur_test cursor for
select a.name from sysobjects a join sysobjects c on a.parent_obj=c.id where a.xtype='f' and c.xtype='u'
open cur_test
fetch next from cur_test into @name
while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
begin
select @tmp1='',@tmp2=''
select @[email protected]+'['+引用的列名+'],',@[email protected]+'['+已引用的列名+'],' from #t where 外鍵名稱[email protected]
insert into #tmp select top 1 'alter table [dbo].['+表名稱+'] add constraint ['[email protected]+'] foreign key ( '+left(@tmp1,len(@tmp1)-1)+' ) references ['+引用的表名+'] ( '+left(@tmp2,len(@tmp2)-1)+' )' from #t where 外鍵名稱[email protected]
end
fetch next from cur_test into @name
end
close cur_test
deallocate cur_test
drop table #t
--第二部分，刪除所有外鍵
declare @string varchar(8000)
while exists(select name from sysobjects where type='f')
begin
select @string='alter table '+b.name+' drop constraint '+a.name+char(13)
from (select parent_obj,name from sysobjects where type='f') a,
(select id,name from sysobjects where objectproperty(id, n'isusertable') = 1) b
where a.parent_obj=b.id
exec(@string)
end
--第三部分，刪除所有表的記錄，并且把identity復位
exec sp_msforeachtable "truncate table ?"
--第4部分，執行#tmp里面的建立外鍵的語句，恢復外鍵
declare cur_test2 cursor for select string from #tmp
open cur_test2
fetch next from cur_test2 into @string
while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
begin
exec(@string)
print @string
end
fetch next from cur_test2 into @string
end
close cur_test2
deallocate cur_test2
drop table #tmp
