NonSecwitter NonSecwitter - 2 months ago 15
SQL Question

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.

Answer

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
'