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