問題描述

我正在使用許多相同的數(shù)據(jù)庫，因此我使用 sp_MSforeachdb 過程以便我可以從表中檢索信息.

I'm working with a lot of databases that are the same so I am using the sp_MSforeachdb procedure so that I can retrieve information from a table.

我遇到的問題是盒子上還有其他數(shù)據(jù)庫沒有表，所以我拋出了無效對象錯誤.

The problem I have encountered is that there are other databases on the box that don't have the table, so I'm throwing invalid object errors.

這是我目前所擁有的，我正在過濾 LoginDatabase，因為它具有相同的表，但我不希望在查詢中使用它.

Here is What I have at the moment, I'm filtering LoginDatabase because it has the same table but I don't want that in the query.

我的問題是，我如何才能將其限制為包含我想從中獲取信息的表的數(shù)據(jù)庫.

My question is, how can I restrict it just to the databases with the table I want to get information back from.

SET NOCOUNT ON

CREATE TABLE #tmpData
(
    DbName VARCHAR(30),
    DbVersion FLOAT
)

exec sp_msforeachdb @command1='
    USE ?;

    INSERT INTO #tmpData
    SELECT ''?'', (SELECT Setting 
        FROM ?.dbo.gl_SysParams 
        WHERE Keyword = ''DatabaseSchema'')
    FROM sysobjects o
    WHERE type=''U'' 
    AND [name] = ''gl_SysParams'' 
    AND ''?'' <> ''LoginDatabase'' ORDER BY [name]
    '   

SET NOCOUNT OFF

SELECT DbName, DbVersion FROM #tmpData ORDER BY DbName

DROP TABLE #tmpData

推薦答案

您可以在每個數(shù)據(jù)庫中使用對 sp_MSforeachtable 的調(diào)用，您可以在其中使用 @WhereAnd 參數(shù)過濾到您感興趣的表 - 它贏了'不存在于您不感興趣的數(shù)據(jù)庫中，因此 sp_MSforeachtable 將在其中運行 0 次，并在每個帶有該表的數(shù)據(jù)庫中運行 1 次.

You could use a call to sp_MSforeachtable within each database, where you use the @WhereAnd parameter to filter down to just the table you're interested in - it won't exist in the database you're not interested in, so sp_MSforeachtable will run 0 times in there, and 1 time in each database with the table.

編輯簡單的例子只是在我的一個隨機服務(wù)器上運行，我知道只有一個數(shù)據(jù)庫有一個 tblClient 表，有一個 ClientID 列(請原諒命名):

Edit Simple example just run against a random server of mine, where I knew only one database had a tblClient table, with a ClientID column (forgive the naming):

create table #t (
    ID int not null
)
exec sp_MSforeachdb 'use ? exec sp_MSforeachtable ''insert into #t(ID) select ClientID from ~'',''~'',@whereand=''and o.name=''''tblClient''''''','?'
select * from #t
drop table #t

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