• 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
    
    '
  • 相关阅读:
    DNS-解析、劫持、污染
    JavaScript-闭包注意事项
    Firefox-常用扩展
    Nginx-server_name匹配规则
    PHP-mysqllib和mysqlnd
    Nginx-缓冲原理及优化
    Linux-NFS原理介绍
    Linux-strace命令
    语音增强的几个基本算法
    【论文:麦克风阵列增强】Microphone Array Post-Filtering For Non-Stationary Noise Suppression
  • 原文地址:https://www.cnblogs.com/jinzhenshui/p/2605163.html
Copyright © 2020-2023  润新知