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 清楚数据 保留所有表结构和约束
exec sp_msforeachtable 'truncate table ''?'''
[/Quote]请具体点行嘛,谢谢。表的结构全部要哦
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
清楚数据 保留所有表结构和约束
此语句不支持一个或多个选项(no_log)。请查阅文档以了解所支持的选项。
@command1='print ''?''',
@command2='truncate table ?',
@postcommand= 'print ''Complete delete!'''你放到你当前数据库执行就知道效果