如何遍历所有 SQL 表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26496864/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to loop through all SQL tables?
提问by NonSecwitter
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
This is my error:
这是我的错误：
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.
Msg 116, Level 16, State 1, Line 19 当子查询没有用 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 <2is just for script testing.
PSAND @count <2仅用于脚本测试。
How can I loop through all of the tables?
如何遍历所有表？
回答by Hiten004
Use this system stored procedure
使用这个系统存储过程
sp_MSforeachtable @command1="select count(*) from ?"
Note: this sp_MSforeachtableis an undocumented stored procedure
注意：这sp_MSforeachtable是一个未记录的存储过程
回答by P?????
Maybe this is what you are looking for
也许这就是你要找的
DECLARE @NAME VARCHAR(100)
DECLARE @SQL NVARCHAR(300)
DECLARE CUR CURSOR FOR
SELECT NAME
FROM SYS.TABLES
WHERE TYPE = 'U'
AND SCHEMA_ID = 1
OPEN CUR
FETCH NEXT FROM CUR INTO @NAME
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'SELECT * FROM WS_LIVE.DBO.'+@NAME+' WHERE GCRECORD IS NOT NULL'
PRINT @SQL
EXEC Sp_executesql
@SQL
FETCH NEXT FROM CUR INTO @NAME
END
CLOSE CUR
DEALLOCATE CUR
回答by Rinoy Ashokan
Some times using Cursor in the SQL is Risk. Below SQL query will traverse through all the tables in a selected data base without using CURSOR.
有时在 SQL 中使用 Cursor 是有风险的。下面的 SQL 查询将遍历选定数据库中的所有表，而不使用 CURSOR。
USE TEST
Declare @TableName nvarchar(256)
SET @TableName = ''
WHILE @TableName IS NOT NULL
BEGIN
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
)
print @TableName -- Your logic will come here
END
GO
Above sql statements will print all the tables in side the TESTdatabase. So instead of print table statement you can give your own sql logic like what you want to do with looping each table and @TableNamewill contain the table name in the present loop.
以上 sql 语句将打印TEST数据库中的所有表。因此，您可以给出自己的 sql 逻辑，而不是打印表语句，就像循环每个表时要执行的操作一样，@ TableName将包含当前循环中的表名。
