第一种方法:单表删除数据、只能一个表一个表删
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 --执行存储
上面的是新建存储、后面的是执行存储、可以单独执行