CREATE PROCEDURE P_SYSTEM_DelAllUserTableData as Begin declare @name varchar(20) Declare Cur Cursor For select Name from sysobjects where xtype='u' and status>=0 declare @SQL Varchar(20) Open Cur Fetch Cur Into @name While @@FETCH_STATUS=0 BEGIN Set @sql='DELETE '+@name+'' Exec(@sql) Fetch Cur Into @name End Close Cur Deallocate cur EndGO--执行 P_SYSTEM_DelAllUserTableData
--先禁用所有外键约束 exec sp_msforeachtable "alter table ? nocheck CONSTRAINT all"--然后删除数据 exec sp_msforeachtable @command1='truncate table ?' ,@whereand='and name not like ''sys%'' and name not in(''特殊表1'',''特殊表2'',''特殊表3'')--再启用所有外键约束 exec sp_msforeachtable "alter table ? check constraint all"
alter table tbname nocheck CONSTRAINT all 上面语句对外銉是没用的 truncate table 是不记录日志的,所以也不会触发触发器。 建义楼主先把所有存在外鍵约束的表找出来,修改关系为级联删除,然后再执行下面语句 exec sp_msforeachtable @command1='truncate table ?' ,@whereand='and name not like ''sys%'' and name not in(''特殊表1'',''特殊表2'',''特殊表3'')
as
Begin
declare @name varchar(20)
Declare Cur Cursor For
select Name from sysobjects where xtype='u' and status>=0
declare @SQL Varchar(20)
Open Cur
Fetch Cur Into @name
While @@FETCH_STATUS=0
BEGIN
Set @sql='DELETE '+@name+''
Exec(@sql)
Fetch Cur Into @name
End
Close Cur
Deallocate cur
EndGO--执行
P_SYSTEM_DelAllUserTableData
Exec SP_MSForEachTable @command1='TRUNCATE Table ? '
,@whereand='and name<>''dtproperties'''
,@whereand='and name<>''dtproperties'''
exec sp_msforeachtable "alter table ? nocheck CONSTRAINT all"--然后删除数据
exec sp_msforeachtable @command1='truncate table ?'
,@whereand='and name not like ''sys%''
and name not in(''特殊表1'',''特殊表2'',''特殊表3'')--再启用所有外键约束
exec sp_msforeachtable "alter table ? check constraint all"
上面语句对外銉是没用的
truncate table
是不记录日志的,所以也不会触发触发器。
建义楼主先把所有存在外鍵约束的表找出来,修改关系为级联删除,然后再执行下面语句
exec sp_msforeachtable @command1='truncate table ?'
,@whereand='and name not like ''sys%''
and name not in(''特殊表1'',''特殊表2'',''特殊表3'')