sqlsql-serverconditional-statementssp-msforeachdb

SQL Iterate Over All Tables


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
'

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'.

EDIT

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
'

Which returns this error:

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

Which refers to this line

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

UPDATE

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.


Solution

  • 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
    '