SQL Server:遍历数据库中包含的所有表 [英] SQL Server : Iterate over all tables contained in a database

问题描述

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

I know using cursors is a bad idea, but I just do not know how to solve this. I would like to iterate over my table list (select [table_name] from information_schema.tables) and issue a specific select statement on each of them.

这是我的尝试:

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 

此操作失败

必须声明表变量"@tableName"

Must declare the table variable "@tableName"

在我的桌子上迭代"如"foreach"的更好方法是什么?预先感谢

What would be a better way to "iterate" like "foreach" over my tables? Thanks in advance

推荐答案

无光标的解决方案-

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = STUFF((
    SELECT '

    SELECT [protocol], [src_ip], [dst_ip], [src_port], [dst_port], [path]
    FROM (
        SELECT *, rn = ROW_NUMBER() OVER (partition by dst_port, protocol ORDER BY id)
        FROM [' + SCHEMA_NAME([schema_id]) + '].[' + name + ']
        WHERE [path] = ''RECEIVE''
    ) a 
    WHERE a.rn = 1 
    ORDER BY dst_port;'
    FROM sys.objects
    WHERE [type] = 'U'
        AND name LIKE 'TS%'
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

PRINT @SQL
EXEC sys.sp_executesql @SQL

输出-

SELECT [protocol], [src_ip], [dst_ip], [src_port], [dst_port], [path]
FROM (
    SELECT *, rn = ROW_NUMBER() OVER (partition by dst_port, protocol ORDER BY id)
    FROM [dbo].[table1]
    WHERE [path] = 'RECEIVE'
) a 
WHERE a.rn = 1 
ORDER BY dst_port;

SELECT [protocol], [src_ip], [dst_ip], [src_port], [dst_port], [path]
FROM (
    SELECT *, rn = ROW_NUMBER() OVER (partition by dst_port, protocol ORDER BY id)
    FROM [dbo].[table2]
    WHERE [path] = 'RECEIVE'
) a 
WHERE a.rn = 1 
ORDER BY dst_port;

这篇关于SQL Server:遍历数据库中包含的所有表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！