sql server清除所有表数据、避开触发器、存储、自增长值、三种方法_sqlserver去掉自增

第一种方法：单表删除数据、只能一个表一个表删

ALTER proc [dbo].[ClearSystem]
as
declare @name sysname,@sqlcommandText varCHAR(max),@int int
set @sqlcommandText=''
set @int=1
declare mycur cursor for
select name from sysobjects where xtype='U' and name not like 'System_*' order by name
open mycur 
fetch next from mycur into @name
while @@FETCH_STATUS=0
begin
 set @sqlcommandText=@sqlcommandText+'truncate table '+@name +CHAR(13)
 set @int=@int+1
 if @int>200
 begin
 PRINT @sqlcommandText
 set @sqlcommandText=''
 set @int=1
 end
 fetch next from mycur into @name
end
close mycur
deallocate mycur

上面的建个存储、下面是执行清除语句

truncate table  inv   --就能把inv表给清除掉

跟delete最大的区别、

1、就是delete只能删除数据、但是如果有触发器的话可能删除不了

2、如果有主键自增长值的话、就不能再从0开始了

第二种方法：清除所有的表数据、也有过滤某些表的条件

declare c cursor for
select NAME from sysobjects where xtype='U' and name not in ('sysclones','sysrowsets','sysrscols')  --框里是过滤不用删除表数据的表名
declare @t varchar(200)
open c
fetch next from c into @t
while @@FETCH_STATUS=0
begin
exec('delete table '+@t)
fetch next from c into @t
end
close c

上面代码也是先建个存储、直接复制代码执行、记的先过滤不用删除表数据的表名

第三种方法：清除所有的表数据、可以保留应用层的主体结构、但是可能针对特殊的应用如：ERP

create proc [dbo].[clearSysData]
as
DECLARE @NAME VARCHAR(50),@SQLCOMMANDTEXT VARCHAR(200),@issqluser int
declare  @groupname varchar(100), @mem_col varchar(100) 
DECLARE CUR_TABLE CURSOR FOR
SELECT NAME FROM SYSOBJECTS WHERE XTYPE='U' AND name NOT LIKE 'sys%' AND NAME NOT IN ('INV_TYPE','INVTYPE','ERPFILES') ORDER BY NAME
OPEN CUR_TABLE
FETCH NEXT FROM CUR_TABLE INTO @NAME
WHILE @@FETCH_STATUS=0
BEGIN
SET @SQLCOMMANDTEXT='TRUNCATE TABLE '+@NAME
EXEC(@SQLCOMMANDTEXT)
FETCH NEXT FROM CUR_TABLE INTO @NAME
END
CLOSE CUR_TABLE
DEALLOCATE CUR_TABLE
 
declare CUR_USER CURSOR FOR
SELECT name,issqluser FROM SYSUSERS WHERE name not in ('dbo','guest','INFORMATION_SCHEMA','sys') AND  name<>'public' and  name not like 'db_%'
OPEN CUR_USER
FETCH NEXT FROM CUR_USER INTO @name,@issqluser
WHILE @@FETCH_STATUS=0
BEGIN
IF @issqluser=1
BEGIN
	exec sp_revokedbaccess @name
	exec sp_droplogin @name
END
BEGIN
	set nocount on 
	set @groupname=@NAME
	create table #SQLDMOTemp (role_col nvarchar(132) NOT NULL, mem_col nvarchar(132) NOT NULL, id_col nvarchar(176))     
	insert into #SQLDMOTemp (role_col, mem_col, id_col) 
	exec sp_helprolemember @groupname
	declare mem_cursor cursor for 
	select mem_col from #SQLDMOTemp  
	open mem_cursor
	fetch next from mem_cursor into @mem_col
	while @@fetch_status=0
	begin
		exec sp_droprolemember @groupname,@mem_col
	fetch next from mem_cursor into @mem_col
	end
	close mem_cursor
	deallocate mem_cursor
	drop table #SQLDMOTemp 
	set nocount off
	exec sp_droprole @groupname	
END
FETCH NEXT FROM CUR_USER INTO @name,@issqluser
END
CLOSE CUR_USER
DEALLOCATE CUR_USER
go
 
exec clearSysData   --执行存储

上面的是新建存储、后面的是执行存储、可以单独执行