Sqlserver 2008：sp_msforeachdb 坑爹的错误陷阱

不多说。看代码

 

-- 一般的写法，可能错误 1
exec sp_msforeachdb
'
if ''?'' like ''edb_a_____''
begin
    if not exists
    (
        select *
        from ?.dbo.sysobjects a inner join ?.dbo.syscolumns b on a.id=b.id
        where a.name=''WFPUSER_T1480'' and b.name=''TC1364''
    )
    begin
        raiserror(''?'',14,1)
    end
    else
        print ''ok''
end

'

-- 一般的写法，可能错误 2
exec sp_msforeachdb
'
use ?

if ''?'' like ''edb_a_____''
begin
    if not exists
    (
        select *
        from sysobjects a inner join syscolumns b on a.id=b.id
        where a.name=''WFPUSER_T1480'' and b.name=''TC1364''
    )
    begin
        raiserror(''?'',14,1)
    end
    else
        print ''ok''
end

'

 

确保正确的写法：

-- 对比 0
exec sp_msforeachdb
'

if ''?'' like ''edb_a_____''
begin
    if not exists
    (
        select *
        from [?].dbo.sysobjects a inner join [?].dbo.syscolumns b on a.id=b.id
        where a.name=''WFPUSER_T1480'' and b.name=''TC1364''
    )
    begin
        raiserror(''?'',14,1)
    end
    else
        print ''ok''
end

'

-- 对比 1 
exec sp_msforeachdb
'
if ''?'' like ''edb_a_____''
begin
    if not exists
    (
        select *
        from [?]..sysobjects a inner join [?]..syscolumns b on a.id=b.id
        where a.name=''WFPUSER_T1480'' and b.name=''TC1364''
    )
    begin
        raiserror(''?'',14,1)
    end
    else
        print ''ok''
end

'

-- 对比 2 
exec sp_msforeachdb
'
use [?]

if ''?'' like ''edb_a_____''
begin

    if not exists
    (
        select *
        from dbo.sysobjects a inner join dbo.syscolumns b on a.id=b.id
        where a.name=''WFPUSER_T1480'' and b.name=''TB0079''
    )
    begin
        raiserror(''?'',14,1)
    end
    else
        print ''ok''
        
end

'

-- 对比 3
exec sp_msforeachdb
'
use [?]

if ''?'' like ''edb_a_____''
begin

    if not exists
    (
        select *
        from sysobjects a inner join syscolumns b on a.id=b.id
        where a.name=''WFPUSER_T1480'' and b.name=''TB0079''
    )
    begin
        raiserror(''?'',14,1)
    end
    else
        print ''ok''
        
end

'