sp_MSforeachtable - parsing of dynamic sql

Question

I recently found an issue whereby I wanted to use the sp_MSforeachtable stored proc to select all tables with the word Transcode in the table name, and to run some SQL on those tables. I managed to write some code which worked, but not perfectly - for those tables which I'd hoped it would gracefully skip over (i.e. those which did not have transcode in the name) it instead threw errors due to certain expected columns (which only exist in the transcode tables) not existing on those tables. The issue seems to be that all SQL is parsed when the stored proc is called, rather than parsing the SQL only when required (e.g. when a condition is met).

The following code works as expected:

exec sp_MSforeachtable '
print ''Table being tested: ?''
if exists (select 1 where ''?'' like ''%Transcode%'')
begin
    print ''    Do Something''
end
else
begin
    print ''    Ignored''
end
'

However, when I then try to add functionality, I get errors from code which would never be run; e.g.

exec sp_MSforeachtable '
print ''Table being tested: ?''
if exists (select 1 where ''?'' like ''%Transcode%'')
begin
    print ''    Do Something''

    insert ? (col1, col2, col3)
    select col1, col2, 1
    from ?
    where col3 = 0

end
else
begin
    print ''    Ignored''
end
'

This time I get the same output as the first one for those where the tablename contains the word Transcode, but for those where it doesn't instead of seeing Ignored, I see:

Msg 207, Level 16, State 1, Line 9

Invalid column name col3

I'm pretty sure this is down to the way the dynamic SQL is parsed, but it's undesirable behaviour. Has anyone come across this before / is there a simple workaround?

This is not urgent as in my case thanks to the columns not existing the errors had the same effect as the if statement anyway, and the valid lines were able to run successfully, but I'm keen to learn in case I need to do something similar soon where this behaviour would cause issues.

Thanks in advance,

JB

ps. code to replicate this behaviour's included below:

create table DemoTranscode1 (id bigint identity(1,1) primary key clustered, col1 nvarchar(10) not null, col2 nvarchar(10)not null, col3 bit not null)
go
create table DemoTable1 (id bigint identity(1,1) primary key clustered, col1 nvarchar(10) not null, col2 nvarchar(10)not null)
go
create table DemoTranscode2 (id bigint identity(1,1) primary key clustered, col1 nvarchar(10) not null, col2 nvarchar(10)not null, col3 bit not null)
go
create table DemoTranscode3 (id bigint identity(1,1) primary key clustered, col1 nvarchar(10) not null, col2 nvarchar(10)not null, col3 bit not null)
go
insert DemoTranscode1
select 'example1', 'demo', 0
union select 'example2', 'demo', 0
union select 'example3', 'demo', 0
union select 'example4', 'demo', 0
insert DemoTable1 select col1, col2 from DemoTranscode1
insert DemoTranscode2 select col1, col2, col3 from DemoTranscode1
insert DemoTranscode3 select col1, col2, col3 from DemoTranscode1

Answer 1

The issue is with the parser. Whether you use a sp_msforeachtable with some condition, still it will parse for each table. So, for other tables - throws error. You may use exec statement to avoid it as shown below -

exec sp_MSforeachtable ' 
print ''Table being tested: ?'' 
if exists (select 1 where ''?'' like ''%Transcode%'') 
begin 
    print ''    Do Something'' 

    exec ( ''insert ? (col1, col2, col3) select col1, col2, 1 from ? where col3 = 0 '') 

end 
else 
begin 
    print ''    Ignored'' 
end 
' 

Answer 2

You can use the @whereand parameter so you don't need to check in your code.

exec sp_MSforeachtable
  @Command1 = 'print "?"',
  @whereand = ' and o.name like ''%Transcode%'''

Update:

I'm pretty sure this is down to the way the dynamic SQL is parsed, but it's undesirable behaviour. Has anyone come across this before

Sure. the code compiles before it is executed and the compiler checks the column names used in the insert statement against the table.

Answer 3

For one, I recommend staying away from undocumented and unsupported procedures like sp_MSForEachTable. They can be changed or even removed from SQL Server at any time, and this specific procedure may have the same symptoms reported by many against sp_MSForEachDb. (See some background here and here, and evidence that they have no intention of fixing, documenting or supporting it here.)

Here is how I would do it:

DECLARE @sql NVARCHAR(MAX);
SELECT @sql = N'';

SELECT @sql = @sql + 'INSERT ' 
  + QUOTENAME(SCHEMA_NAME([schema_id]))
  + '.' + QUOTENAME(name) + ' (col1, col2, col3)
  SELECT col1, col2, 1 FROM '
  + QUOTENAME(SCHEMA_NAME([schema_id]))
  + '.' + QUOTENAME(name)
  + ' WHERE col3 = 0;'
FROM sys.tables 
WHERE name LIKE '%Transcode%';

PRINT @sql;
-- EXEC sp_executesql @sql;

The nice thing about this is it's easy to validate the output before executing.