• 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
    
    '
  • 相关阅读:
    LaTeX中表格多行显示的最简单设置方法
    获取Google音乐的具体信息(方便对Google音乐批量下载)
    移动硬盘提示格式化解决的方法,未正确删除导致不能读取文件提示格式化解决方式
    Android Service 服务(一)—— Service
    华为C8816电信版ROOT过程
    Linux crontab 命令格式与具体样例
    Python用subprocess的Popen来调用系统命令
    我的EJB学习历程
    接口和逻辑--多进程或单一进程
    uva 11354
  • 原文地址:https://www.cnblogs.com/jinzhenshui/p/2605163.html
Copyright © 2020-2023  润新知