sp_MSforeachtable-动态SQL的解析 [英] sp_MSforeachtable - parsing of dynamic sql

问题描述

我最近发现了一个问题，我想使用sp_MSforeachtable存储的proc选择表名称中带有Transcode单词的所有表，并在这些表上运行一些SQL.我设法写了一些行得通的代码，但不是很完美-对于那些我希望它能正常跳过的表(即那些名称中没有转码的表)，它却由于某些预期的列而抛出了错误(仅存在于转码表中)不存在于这些表中.问题似乎是调用存储的proc时将解析所有SQL，而不是仅在需要时(例如，满足条件时)解析SQL.

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

以下代码按预期工作:

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
'

这次，对于那些表名包含单词Transcode的人，我得到的输出与第一个相同，但是对于那些没有看到忽略的人，我会看到:

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:

第207条消息，状态16，状态1，第9行

Msg 207, Level 16, State 1, Line 9

无效的列名称col3

Invalid column name col3

我很确定这取决于动态SQL的解析方式，但这是不受欢迎的行为.有没有人遇到过这个/是否有一个简单的解决方法?

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?

这并不紧急，因为在我的情况下，由于不存在的列，错误仍然与if语句具有相同的效果，并且有效行能够成功运行，但是我很想学习以防万一尽快做类似的事情，这种行为会引起问题.

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.

预先感谢

JB

ps.复制此行为的代码如下:

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

推荐答案

对于一个，我建议远离诸如sp_MSForEachTable之类的未记录且不受支持的过程.它们可以随时更改，甚至可以从SQL Server中删除，并且此特定过程可能具有许多人针对sp_MSForEachDb报告的相同症状. (请参阅一些背景此处，并证明他们无意修复，记录或支持它

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

这是我要怎么做:

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.

这篇关于sp_MSforeachtable-动态SQL的解析的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！