SQL 遍历所有表

2022-01-21 00:00:00 sql conditional sql-server sp-msforeachdb

我正在运行以下代码以从具有特定列的所有表中提取所有相关行.外部 IF 应该检查该列是否存在于该迭代的表中.如果不是，它应该完成该迭代并移动到下一个表.如果表有 GCRecord 列，那么它应该检查该表是否会返回任何记录.如果没有要返回的记录，它应该结束该迭代并移动到下一个表.如果有记录，应该在SSMS中显示.

I am running the following code to extract all relevant rows from all tables that have a particular column. The outer IF is supposed to check if the column exists on the table for that iteration. If not, it should finish that iteration and move to the next table. If the table has the GCRecord column, it should then check to see if that table will return any records. If there are no records to return, it should end that iteration and move on to the next table. If there are records, it should display them in SSMS.

USE WS_Live

EXECUTE sp_MSforeachtable 
'
    USE WS_Live

    IF EXISTS(  SELECT * 
                FROM sys.columns
                WHERE columns.Object_ID = Object_ID(''?'')
                AND Name = ''GCRecord''
             ) 
    BEGIN
        IF EXISTS (SELECT * FROM ? WHERE GCRecord IS NOT NULL)

        BEGIN
            SELECT * FROM ? WHERE GCRecord IS NOT NULL
        END
    END
'

这似乎有效，因为 SSMS 只返回具有有效条目的网格.我不明白的是:为什么我仍然收到这些错误?

It seems to work because SSMS is only returning grids with valid entries. What I don't understand is: Why am I still getting these errors?

Msg 207, Level 16, State 1, Line 10
Invalid column name 'GCRecord'.
Msg 207, Level 16, State 1, Line 13
Invalid column name 'GCRecord'.

编辑

使用建议后，我有这个:

After using the suggestions, I have this: 

USE WS_Live

EXECUTE sp_MSforeachtable 
'
    USE WS_Live

    IF EXISTS(SELECT * FROM sys.columns WHERE columns.Object_ID = Object_ID(''?'')AND Name = ''GCRecord'') 

    BEGIN
    IF EXISTS (SELECT * FROM ? WHERE GCRecord IS NOT NULL)

            BEGIN
               EXEC('' SELECT * FROM ? WHERE GCRecord IS NOT NULL'')
            END
    END
'

返回此错误:

Msg 207, Level 16, State 1, Line 7
Invalid column name 'GCRecord'.

指的是这一行

IF EXISTS(SELECT * FROM sys.columns WHERE columns.Object_ID = Object_ID(''?'')AND Name = ''GCRecord'')

更新

我尝试嵌套 EXEC 语句但不起作用，但使用选定的答案我得到了我正在寻找的结果而没有错误.

I tried nesting EXEC statements which did not work, but using the selected answer I got the results I was looking for without the errors.

推荐答案

在begin内部使用动态查询避免内部预编译代码，因为表不包含列'GCRecord'

Use Dynamic query inside begin to avoid inner pre-compilation of code, for tables do not contain column 'GCRecord'

USE WS_Live
GO

EXECUTE sp_MSforeachtable 
'
    IF EXISTS(  SELECT * 
                FROM sys.columns
                WHERE columns.Object_ID = Object_ID(''?'')
                AND Name = ''GCRecord''
             ) 
    BEGIN

          EXEC(''          
                  IF EXISTS (SELECT * FROM ? WHERE GCRecord IS NOT NULL)
                 BEGIN
                         SELECT * FROM ? WHERE GCRecord IS NOT NULL
                 END
              '')
    END
'

相关文章