求一存储过程，要求内详

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 

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 LZ的数据库 with no_log

dbcc shrinkdatabase( LZ的数据库 )

dbcc updateusage( LZ的数据库 )



-- 查看表空间(概数)

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

declare cur cursor for 

    select [name] from sysobjects where xtype = 'u'

declare @name varchar(256)

declare @sql varchar(8000)

open cur

fetch next from cur into @name

    while(@@fetch_status=0)

    begin

        set @sql='delete from '+@name +' dbcc checkident ('+@name+',reseed,0)'

        print @sql

        exec (@sql)

        fetch next from cur into @name

    end

close cur

deallocate cur

--定义游标，把当前数据库中的用户表，取出来，放到游标cur中。

declare cur cursor for 

    select [name] from sysobjects where xtype = 'u'

--声明表名变量

declare @name varchar(256)

--要执行：删除表数据，重置表标识列的sql语句

declare @sql varchar(8000)

--打开游标，循环遍历表，执行sql语句。

open cur

fetch next from cur into @name

    while(@@fetch_status=0)

    begin

        set @sql='delete from '+@name +' dbcc checkident (t,reseed,0)'

        print @sql

        --exec (@sql)

        fetch next from cur into @name

    end

close cur

deallocate cur

declare cur cursor for 

	select [name] from sysobjects where xtype = 'u'

declare @name varchar(256)

declare @sql varchar(8000)

open cur

fetch next from cur into @name

	while(@@fetch_status=0)

	begin

		set @sql='delete from '+@name +' dbcc checkident (t,reseed,0)'

		print @sql

		--exec (@sql)

		fetch next from cur into @name

	end

close cur

deallocate cur

我们也可以用微软不公开的存储过程来实现此操作，这就非常简单了，代码如下：

--Removed CHECK Constraint-------------------------

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' --NOCHECK Constraints

print 'All Constraints Disable'

go



--truncate All tables  if trying to empty the database 

 --- Ensure the T_X_ref database is located on a different database



-------------  Truncate All Tables from Model ----------------

-----To limit tables a table with sub model tables must be created  and used joins-----

EXEC sp_MSForEachTable 'truncate TABLE ? '

print 'All tables truncated'

go

-- =========================================

-- -----------t_mac 小编-------------------

   --------------------希望有天成为大虾---- 

-- =========================================

-->测试

IF OBJECT_ID('tb') IS NOT NULL

DROP TABLE tb

GO

CREATE TABLE tb(  a int primary key )

go

 

IF OBJECT_ID('ta') IS NOT NULL

DROP TABLE ta

GO

CREATE TABLE ta(  b int primary key,a int references tb(a) )

go

insert into tb select 1 union all  select 2

insert into ta select 1,1 union all select 4,2

go

insert into ta select 3,3

/*

*/

--约束暂时失去作用

EXEC sp_MSForEachTable 'ALTER TABLE ? nocheck constraint ALL ' --NOCHECK Constraints

--清空

exec sp_msforeachtable "delete from  ?"

select * from tb 

select * from ta

/*

a

-----------

*/

/*

b           a

----------- --------

*/