SQL Iterate Over All Tables

Question

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.

My guess is that SSMS is still compiling the inner query even if it wouldn't execute it based on the EXISTS result. — D Stanley
– D Stanley, Commented Oct 22, 2014 at 19:20

AA.SC · Accepted Answer · 2014-10-22 19:37:01Z

2

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
'

edited Oct 22, 2014 at 19:37

answered Oct 22, 2014 at 19:30

AA.SC

3775 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user4151308 · Accepted Answer · 2014-10-22 19:34:11Z

2

You are very close. Use "EXEC"

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
        EXEC(''SELECT * FROM ? WHERE GCRecord IS NOT NULL'')
    END
'

answered Oct 22, 2014 at 19:34

user4151308

2 Comments

Jonathon Anderson Over a year ago

Thanks. This has gotten me close. I added an update.

Jonathon Anderson Over a year ago

Got it. Thanks for the answer, I tried nesting EXEC() statements, which apparently didn't work. I moved them as @AA.SC showed below, and I got rid of all of the errors.

Hiten004 · Accepted Answer · 2014-10-22 19:17:34Z

0

Please try this dynamic sql. remove comment from exec when you are ready to run

declare @t varchar(max) = ''

SELECT @t =  @t + 'SELECT * FROM ' + a.name + ' WHERE GCRecord IS NOT NULL;' + char(13)
                FROM sys.columns b join sys.objects a  on
                 b.Object_ID = a.Object_ID
                WHERE b.Name ='CreateDt'

Print  @t
--exec (@t)

answered Oct 22, 2014 at 19:17

Hiten004

2,5011 gold badge24 silver badges34 bronze badges

Comments

Alex Hinton · Accepted Answer · 2016-09-15 15:03:00Z

0

Use @whereand to simplify:

exec sp_MSforeachtable 
@command1='select * from ? where GCRecord is not null', 
@whereand='and exists(select 1 from sys.columns c where c.object_id = o.id 
                      and c.name = ''GCRecord'')'

edited Sep 15, 2016 at 15:03

answered Sep 15, 2016 at 14:57

Alex Hinton

1,61815 silver badges15 bronze badges

Collectives™ on Stack Overflow

SQL Iterate Over All Tables

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related