数据库数据清理

批量删除数据库数据，保留指定的数据量

--每个表保留500条数据
declare @strDBName varchar(200)
DECLARE @strSQL AS VARCHAR(1000)
  declare order_cursor cursor 
    for
SELECT a.name
FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id
WHERE (a.type = 'u') AND (b.indid IN (0, 1))
and  b.rows>500  --表中数据大于500条的表
ORDER BY a.name
 open order_cursor
    --开始循环游标变量--
    fetch next from order_cursor into @strDBName
    while @@FETCH_STATUS = 0    --返回被 FETCH语句执行的最后游标的状态--
        begin 
        SET @strSQL='with temp as ( select row_number() over(order by getdate()) as rownum,* from ['+@strDBName+'] ) delete from temp where rownum >500'        
            --print (@strSQL);
            exec(@strSQL)       
           fetch next from order_cursor into @strDBName   --转到下一个游标，没有会死循环
        end    
    close order_cursor  --关闭游标
    deallocate order_cursor   --释放游标      

 

转载于:https://www.cnblogs.com/AlexLeeLi/p/10410528.html