sql-server - SQL Server : Iterate over all tables contained in a database

我知道使用游标是一个坏主意，但我只是不知道如何解决这个问题。我想遍历我的表列表 ( select [table_name] from information_schema.tables ) 并对每个表发出特定的 select 语句。

这是我的尝试:

DECLARE c CURSOR READ_ONLY FAST_FORWARD FOR
    SELECT [TABLE_NAME]
    FROM INFORMATION_SCHEMA.TABLES
    WHERE [TABLE_NAME] like 'TS%'

DECLARE @tableName char(7)

OPEN c

FETCH NEXT FROM c INTO @tableName
WHILE (@@FETCH_STATUS = 0)
BEGIN
    -- which distinct ports has been used by inbound connections (listening sockets)?
    SELECT [protocol], [src_ip], [dst_ip], [src_port], [dst_port], [path]
    FROM (SELECT *, ROW_NUMBER() over (partition by [dst_port], [protocol] order by [id]) as RowNumber
             FROM @tableName -- <<<<<< THIS IS WHERE IT FAILS
             where [path] = 'RECEIVE') as a
    WHERE a.RowNumber = 1 order by [dst_port];

    FETCH NEXT FROM c into @tableName
END
CLOSE c
DEALLOCATE c

这失败了



在我的表上“迭代”像“foreach”这样的更好的方法是什么？提前致谢

最佳答案

对于这种情况，我认为没有比您已经在做的更好的执行方式了。有未记录的存储过程 sp_MSforeachtable 但我不推荐它，因为它不受 Microsoft 支持，并且在幕后将执行与您已经在做的类似的过程。

关于sql-server - SQL Server : Iterate over all tables contained in a database，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20904223/