如何遍历所有SQL表? [英] How to loop through all SQL tables?

问题描述

我们有一个软件不会删除不再需要的条目.为了了解服务器中有多少数据正在浪费，并准备进行大型清理操作，我试图遍历所有表并拉出标记为删除的记录.这就是我正在使用的:

We have a piece of software that does not delete entries we no longer want. In order to get a feel for how much data is wasting away in our server and prepare for a big cleanup operation, I am trying to loop through all of the tables and pull the records that are marked for deletion. This is what I'm working with:

DECLARE @total INT
DECLARE @count INT
DECLARE @name NVARCHAR(25)
DECLARE @rn INT

SET @total = (SELECT COUNT(Name) FROM sys.tables)
SET @count = 1
SET @rn = (SELECT ROW_NUMBER() OVER(ORDER BY Name) FROM sys.tables)   

WHILE @count <= @total AND @count < 2
    BEGIN
        SET @name = (   SELECT Name, ROW_NUMBER() OVER(ORDER BY Name)
                        FROM sys.tables 
                        WHERE @rn = @count
                     )

        EXEC('SELECT * FROM WS_Live.dbo.' + @name + ' WHERE GCRecord IS NOT NULL')
        SET @count += 1         
    END

这是我的错误:

第16层，状态1，第19行的消息116 如果未使用EXISTS引入子查询，则只能在选择列表中指定一个表达式.

Msg 116, Level 16, State 1, Line 19 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

我意识到我的错误可能与选择该行中的两列

I realize that my error probably has to do with selecting two columns in the line

        SET @name = (   SELECT Name, ROW_NUMBER() OVER(ORDER BY Name)
                        FROM sys.tables 
                        WHERE @rn = @count
                     )

但是，我不确定如何确保选择下一行.

but, I'm not sure how else to ensure that I am selecting the next row.

P.S. AND @count <2仅用于脚本测试.

P.S. AND @count <2 is just for script testing.

如何遍历所有表?

推荐答案

使用此系统存储过程

sp_MSforeachtable @command1="select count(*) from ?"

示例代码

注意:此sp_MSforeachtable是未记录的存储过程

Note: this sp_MSforeachtable is an undocumented stored procedure

这篇关于如何遍历所有SQL表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！