保存数据库中其他对象不变，删除数据库中所有数据的实现方法
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
