删除多余数据行的存储过程

--缘由：要把数据库给别人，但每表中的纪录最多只想给3条，于是要删除多余数据行
--注意：为避免数据参照导致删除出问题，建议先删除原有外键。
--存储过程spDeleteData，删除表名参数所对应的表的纪录，最多保留3条纪录
--set rowcount 、动态SQL、全局临时表的应用

create proc spDeleteData(@tn nvarchar(30)) as
begin
	set nocount on
	if object_id('tempdb..##t') is not null drop table ##t
	exec('select count(*) cnt into ##t from ' + @tn)
	declare @row int
	select @row=cnt from ##t
	if @row>3
	begin		
		set @row=@row-3
		set rowcount @row
		exec('delete from ' + @tn)
		set rowcount 0
	end
end

go

--存储过程spDeleteDataByTableName，调用spDeleteData删除数据库中所用表的纪录，每表最多保留3条纪录
--set rowcount 、while循环、局部临时表的应用

create proc spDeleteDataByTableName as
begin
	if object_id('tempdb..#t') is not null drop table #t
	select [name] into #t from sysobjects where type='u'
	declare @tn nvarchar(30)
	set rowcount 1
	select @tn=[name] from #t
	while @@rowcount>0
	begin
		exec spDeleteData @tn --嵌套调用存储过程spDeleteData	
		select @tn=[name] from #t
		delete from #t		
	end	
	set rowcount 0
end

go

exec spDeleteDataByTableName--调用存储过程spDeleteDataByTableName