SQL遍历所有表 [英] SQL Iterate Over All Tables

问题描述

我正在运行以下代码，以从具有特定列的所有表中提取所有相关行。外部的 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.

推荐答案

开始使用内部动态查询来避免代码的内部预编译，因为表不包含列' 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
'

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