問題描述

我所在的地方有一個在大型機系統(tǒng)上運行的軟件包.大型機每晚都會轉(zhuǎn)儲到 sql server，這樣我們的每個客戶端在服務(wù)器中都有自己的數(shù)據(jù)庫.服務(wù)器實例中還有一些其他數(shù)據(jù)庫，以及一些沒有數(shù)據(jù)的舊客戶端數(shù)據(jù)庫.

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.

我們經(jīng)常需要跨所有客戶運行報告或檢查數(shù)據(jù).我希望能夠使用 sp_msforeachdb 或類似的東西運行查詢，但我不確定如何從列表中過濾不需要的數(shù)據(jù)庫.關(guān)于這如何工作的任何想法?

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?

我們?nèi)栽谑褂?SQL Server 2000，但應(yīng)該會在幾個月后遷移到 2005.

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

更新:
我覺得我問這個問題做得不好，所以我要澄清我的目標，然后發(fā)布我最終使用的解決方案.

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.

我想在這里完成的是讓程序員可以輕松地處理要在其程序中使用的查詢，使用一個客戶端數(shù)據(jù)庫編寫查詢，然后幾乎立即運行(測試)在一個客戶端的數(shù)據(jù)庫上設(shè)計和構(gòu)建的代碼在所有 50 個左右的客戶端數(shù)據(jù)庫上，幾乎沒有修改.

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 變量設(shè)置為您的查詢文本.很容易變成存儲過程.它是動態(tài) sql，但同樣:它僅用于開發(fā)(著名的遺言；)).缺點是您仍然需要轉(zhuǎn)義查詢中使用的單引號，并且大部分時間您最終會在選擇列表中放置一個額外的 ''?'' 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.

除非我今天得到另一個非常好的主意，否則我想把它變成一個存儲過程，并使用臨時表將所有結(jié)果放在一個結(jié)果集中作為表值函數(shù)的一個版本(僅適用于選擇查詢).

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

推薦答案

只需將要執(zhí)行的語句包裝在 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
"

這篇關(guān)于幫助處理類似 sp_msforeachdb 的查詢的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！