帮助处理类似 sp_msforeachdb 的查询

问题描述

我所在的地方有一个在大型机系统上运行的软件包.大型机每晚都会转储到 sql server，这样我们的每个客户端在服务器中都有自己的数据库.服务器实例中还有一些其他数据库，以及一些没有数据的旧客户端数据库.

Where I'm at we have a software package running on a mainframe system. The mainframe makes a nightly dump into sql server, such that each of our clients has it's own database in the server. There are a few other databases in the server instance as well, plus some older client dbs with no data.

我们经常需要跨所有客户运行报告或检查数据.我希望能够使用 sp_msforeachdb 或类似的东西运行查询，但我不确定如何从列表中过滤不需要的数据库.关于这如何工作的任何想法?

We often need to run reports or check data across all clients. I would like to be able to run queries using sp_msforeachdb or something similar, but I'm not sure how I can go about filtering unwanted dbs from the list. Any thoughts on how this could work?

我们仍在使用 SQL Server 2000，但应该会在几个月后迁移到 2005.

We're still on SQL Server 2000, but should be moving to 2005 in a few months.

更新:
我觉得我问这个问题做得不好，所以我要澄清我的目标，然后发布我最终使用的解决方案.

Update:
I think I did a poor job asking this question, so I'm gonna clarify my goals and then post the solution I ended up using.

我想在这里完成的是让程序员可以轻松地处理要在其程序中使用的查询，使用一个客户端数据库编写查询，然后几乎立即运行(测试)在一个客户端的数据库上设计和构建的代码在所有 50 个左右的客户端数据库上，几乎没有修改.

What I want to accomplish here is to make it easy for programmers working on queries for use in their programs to write the query using one client database, and then pretty much instantly run (test) code designed and built on one client's db on all 50 or so client dbs, with little to no modification.

考虑到这一点，这是我目前位于 Management Studio 中的代码(部分混淆):

With that in mind, here's my code as it currently sits in Management Studio (partially obfuscated):

use [master]
declare @sql varchar(3900) 

set @sql = 'complicated sql command added here'

-----------------------------------
declare @cmd1 varchar(100)
declare @cmd2 varchar(4000)
declare @cmd3 varchar(100)
set @cmd1 = 'if ''?'' like ''commonprefix_%'' raiserror (''Starting ?'', 0, 1) with nowait'
set @cmd3 = 'if ''?'' like ''commonprefix_%'' print ''Finished ?'''
set @cmd2 = 
    replace('if ''?'' like ''commonprefix_%'' 
    begin 
        use [?]
        {0} 
    end', '{0}', @sql)

exec sp_msforeachdb @command1 = @cmd1, @command2 = @cmd2, @command3 = @cmd3

这方面的好处是您只需将@sql 变量设置为您的查询文本.很容易变成存储过程.它是动态 sql，但同样:它仅用于开发(著名的遗言；)).缺点是您仍然需要转义查询中使用的单引号，并且大部分时间您最终会在选择列表中放置一个额外的 ''?'' As ClientDB 列，否则它运作良好.

The nice thing about this is all you have to do is set the @sql variable to your query text. Very easy to turn into a stored procedure. It's dynamic sql, but again: it's only used for development (famous last words ;) ). The downside is that you still need to escape single quotes used in the query and much of the time you'll end up putting an extra ''?'' As ClientDB column in the select list, but otherwise it works well enough.

除非我今天得到另一个非常好的主意，否则我想把它变成一个存储过程，并使用临时表将所有结果放在一个结果集中作为表值函数的一个版本(仅适用于选择查询).

Unless I get another really good idea today I want to turn this into a stored procedure and also put together a version as a table-valued function using a temp table to put all the results in one resultset (for select queries only).

推荐答案

只需将要执行的语句包装在 IF NOT IN 中:

Just wrap the statement you want to execute in an IF NOT IN:

EXEC    sp_msforeachdb  "
IF      '?'     NOT IN ('DBs','to','exclude')   BEGIN
        EXEC    sp_whatever_you_want_to
END
"

这篇关于帮助处理类似 sp_msforeachdb 的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！