--如果有数据的表还涉及到外键的因素 那么 if( object_id('pr_DataClear') is not null ) drop procedure pr_DataClear go create procedure pr_DataClear as begin transaction declare @cTblName varchar(128) declare cur_Clear cursor for select rtrim(name) from sysobjects where type = 'U' order by crdate desc
open cur_Clear declare @cSQL varchar(255) fetch next from cur_Clear into @cTblName while( @@fetch_status = 0) begin set @cSQL = 'delete from ' + @cTblName print @cSQL exec( @cSQL ) if( ident_seed(@cTblName) is not null ) begin dbcc checkident( @cTblName, reseed, 0 ) print '有种子且成功重置为1' end fetch next from cur_Clear into @cTblName end close cur_Clear deallocate cur_Clear commit go --执行 exec pr_DataClear-- 截断日志 backup log pms with no_log dbcc shrinkdatabase( pms ) dbcc updateusage( pms ) --查看表空间 select object_name(id) as 表名, (rtrim(8*reserved/1024) + 'MB') as 总量, (rtrim(8*dpages/1024) + 'MB') as 已使用, (rtrim(8*(reserved-dpages)/1024) + 'MB') as 未使用, (rtrim(8*dpages/1024-rows/1024*minlen/1024) + 'MB' ) as 空隙 from sysindexes where indid=1 order by reserved desc
参考老大的
sp_MSforeachtable 'Delete from ?'sp_MSforeachtable @command1="Delete from ? "
sp_MSforeachtable 'Truncate Table ?'
use Data_a
go
sp_MSforeachtable 'Delete from ?'
EXEC sp_MSforeachtable @command1="print '?'",
@command2= "SELECT count(*) FROM ? "
--delete
EXEC sp_MSforeachtable @command1="print '?'",
@command2= "truncate table ? "
drop procedure pr_DataClear
go create procedure pr_DataClear as begin transaction declare @cTblName varchar(128)
declare cur_Clear cursor for select rtrim(name) from sysobjects where type = 'U' order by crdate desc
open cur_Clear
declare @cSQL varchar(255)
fetch next from cur_Clear into @cTblName
while( @@fetch_status = 0)
begin
set @cSQL = 'delete from ' + @cTblName
print @cSQL
exec( @cSQL )
if( ident_seed(@cTblName) is not null )
begin
dbcc checkident( @cTblName, reseed, 0 )
print '有种子且成功重置为1'
end
fetch next from cur_Clear into @cTblName
end
close cur_Clear
deallocate cur_Clear
commit
go
--执行
exec pr_DataClear-- 截断日志
backup log pms with no_log
dbcc shrinkdatabase( pms )
dbcc updateusage( pms ) --查看表空间
select object_name(id) as 表名,
(rtrim(8*reserved/1024) + 'MB') as 总量,
(rtrim(8*dpages/1024) + 'MB') as 已使用,
(rtrim(8*(reserved-dpages)/1024) + 'MB') as 未使用,
(rtrim(8*dpages/1024-rows/1024*minlen/1024) + 'MB' ) as 空隙 from sysindexes
where indid=1
order by reserved desc
exec sp_MSforeachtable @command1="print '?'",
@command2= "SELECT count(*) FROM ? " exec sp_MSforeachtable @command1="print '?'",
@command2= "truncate table ? "
关于MSforeachtable和sp_MSforeachdb
sp_MSforeachtable 'if exists(select 1 from ?)
print ''?'''
--2
sp_MSforeachtable 'Truncate Table ?'