views:

9

answers:

2

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
+1  A: 

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
Damien_The_Unbeliever
So wrap the sp_MSforeachtable within the @command1 of sp_MSforeachdb?
Sres
@Sres - yep. The quotes can be a little confusing, and of course, you need to specify the @replacechar for one of the stored procs
Damien_The_Unbeliever
I'm hitting a problem with Ambiguous column name 'name' now, I've aliased the sysobjects from the original above, what table is the @whereand looking?
Sres
Code looks awful pasted here...
Sres
@Sres - @whereand is appended to a query against sysobjects, aliased as "o", within the body of sp_MSforeachtable. I'd suggest avoiding using the alias "o" within your own query, and ensure you use aliases for all table references. You can inspect the code of sp_MSforeachtable (and db) if you need to see how they're working.
Damien_The_Unbeliever
If I could reach through the screen and hug you I would Damien, it worked perfectly. I'll post the solution now. Thank you
Sres
@Sres - I can edit this answer, if you'd like me to include more of my comments in it - which further bits in the comments helped?
Damien_The_Unbeliever
I've posted it below mate, I've given you the tick, as it was the lead.
Sres
A: 

Solution with the help of Damien_the_Unbeliever

SET NOCOUNT ON

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

exec sp_MSforeachdb @command1 = '
    USE ?;

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

SET NOCOUNT OFF

SELECT DbName, DbVersion FROM #tmpData ORDER BY DbName

DROP TABLE #tmpData
Sres