sp_MSforeachtable - 动态sql的解析(sp_MSforeachtable -

最近，我发现了一个问题，即我想用sp_MSforeachtable存储过程来选择在表名的字转码的所有表，以及运行在这些表的一些SQL。我设法写一些代码的工作，但并不完美 - 对此，我希望它会优雅地跳过这些表（即那些没有在名称转码），而是全身心的错误由于某些预期的列（其中仅存在的转码表）不存在这些表。这个问题似乎是，当存储过程被称为所有SQL解析，而不是仅在需要时解析SQL（例如，当条件满足）。

下面的代码按预期工作：

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

然而，当我再尝试添加功能，我得到的代码错误，这些错误将永远不会运行; 例如

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
'

这一次，我得到的输出作为第一个为那些表名包含单词转码相同，但对于那些它并不代替见状忽略，我看到：

消息207，级别16，状态1，9号线
无效的列名COL3

我敢肯定，这是倒在动态SQL被解析的方式，但它的不良行为。有没有人碰到这种前/有一个简单的解决方法吗？

这是不急在我的情况下，由于不存在错误的列有作为if语句相同的效果，无论如何，和有效行能够成功运行，但我渴望的情况下，去学习，我需要做些什么类似很快在那里这种行为会造成问题。

提前致谢，

PS。代码复制此行为的下面包括：

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:

首先，我建议住来自像无证和不支持的程序走sp_MSForEachTable 。他们可以更改或在任何时候从SQL Server甚至去除，这具体程序可能有许多反对报告相同的症状sp_MSForEachDb 。（见一些背景这里和这里，和证据，他们没有固定，记录或支持它的意图在这里。）

这里是我会怎么做：

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;

关于这个的好处是很容易在执行之前验证输出。

Answer 2:

您可以使用@whereand参数，所以你不需要在你的代码进行检查。

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

更新：

我敢肯定，这是倒在动态SQL被解析的方式，但它的不良行为。有没有人碰到这之前

当然。在执行之前的代码编译，编译器会检查对表的INSERT语句中使用的列名。

Answer 3:

问题是与分析器。无论是使用sp_msforeachtable一些条件，仍然会分析每个表。因此，对于其他表 - 抛出错误。您可以使用exec语句，以避免它，如下图所示 -

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 
'

文章来源: sp_MSforeachtable - parsing of dynamic sql