数据库备份时，数据过多，批量脚本整理，查询表所占空间

--找出表占据空间最大的
create table #t(name varchar(255), rows bigint, reserved varchar(20), data varchar(20), index_size varchar(20), unused varchar(20))
exec sp_MSforeachtable "insert into #t exec sp_spaceused '?'"
select * from #t order by rows desc;
select SUM(Cast(REPLACE(data,' KB','') as int)) as 总花费容量 from #t;
drop table #t

--调用删除存储过程
select top 1* from HR_StaffAppraise;
select COUNT(*) from HR_StaffAppraise;
exec pro_DeleteRubish 'HR_StaffAppraise','ApprProposalId','staffid','apprperiodvalue';

--存储过程原型 分组后保留该组至少一条数据
delete from @tableName where @idName not in
(
 select @idName from ( 
  select @idName,row_number() over(partition by @groupName order by @orderName desc) rn 
  from @tableName 
 ) t where t.rn <=1
)

--存储过程
create PROCEDURE pro_DeleteRubish
 @tableName nvarchar(200),
 @idName nvarchar(200),
 @groupName nvarchar(200),
 @orderName nvarchar(200) 
AS
BEGIN
 declare @sql nvarchar(1000)
 set @sql='delete from '+ @tableName+' where '+@idName+' not in(select '+@idName+' from (select '+@idName+',row_number() over(partition by '+@groupName+' order by '+@orderName+' desc) rn 
  from '+@tableName+' 
 ) t where t.rn <=1
 )';
print @sql
exec (@sql)
END
GO

 

转载于:https://www.cnblogs.com/shenqiboy/p/3338661.html