34,576
社区成员
发帖
与我相关
我的任务
分享
--先禁用所有外键约束
exec sp_msforeachtable "alter table ? nocheck CONSTRAINT all"
--然后删除数据
exec sp_msforeachtable @command1="truncate table ? ;",
@whereand='and schema_id = (select schema_id from sys.schemas )'
--再启用所有外键约束
exec sp_msforeachtable "alter table ? check constraint all"
select 'truncate table '+ name from sys.tables where type='U'
复制这个结果,然后执行即可!
exec sp_msforeachtable @command1="truncate table ? ;",
@whereand='and schema_id = (select schema_id from sys.schemas )'
--删除外键约束
DECLARE c1 cursor for
select 'alter table ['+ object_name(parent_obj) + '] drop constraint ['+name+']; '
from sysobjects
where xtype = 'F'
open c1
declare @c1 varchar(8000)
fetch next from c1 into @c1
while(@@fetch_status=0)
begin
exec(@c1)
fetch next from c1 into @c1
end
close c1
deallocate c1
--删除表
DECLARE c2 cursor for
select 'drop table ['+name +']; '
from sysobjects
where xtype = 'u'
open c2
declare @c2 varchar(8000)
fetch next from c2 into @c2
while(@@fetch_status=0)
begin
exec(@c2)
fetch next from c2 into @c2
end
close c2
deallocate c2
exec sp_msforeachtable @command1="truncate table ? ;",
@whereand='and schema_id = (select schema_id from sys.schemas where [name] not in(''khda'',''khzh'',''khzmx''))'
-- 打开隐式事务
SET IMPLICIT_TRANSACTIONS ON
-- 执行动态删除
begin tran
declare @sql varchar(max);
set @sql='';
select @sql=@sql+'delete from '+
QUOTENAME(SCHEMA_NAME([schema_id]))+'.'+QUOTENAME([name])+';'
from A.sys.tables where is_ms_shipped =0
--select @sql;
EXEC(@sql);
-- 正确,则提交事务,确认删除
commit tran
-- 不正确,则回滚事务,取消删除
rollback tran
-- 关闭隐式事务
SET IMPLICIT_TRANSACTIONS OFF
exec sp_MSforeachtable @command1=‘truncate from ?’
--删除当前数据库所有表中的数据
sp_MSforeachtable @command1='Delete from ?'
sp_MSforeachtable @command1 = "TRUNCATE TABLE ?"