Hi,
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