August 13, 2009 at 10:16 am
Is there an easy way to run one select statement against multiple databases. Ex.
run this script against databases_aaa through database_zzz
SELECT [ABC]
,[123]
FROM [database].[dbo].[tabledata]
Where FieldName = 'QWERTY'
GO
August 13, 2009 at 10:18 am
Hi,
You could use the undocumented function sp_msforeachdb.
Simple as;
sp_msforeachdb @command1 = 'SELECT ''123,?'''
The ? is the variable, in this case the database name.
Hope this helps.
August 13, 2009 at 10:19 am
or in your case;
sp_msforeachdb @command1 = 'SELECT [ABC]
,[123]
FROM [dbo].[tabledata]
Where FieldName = ''QWERTY'''
August 13, 2009 at 10:25 am
You may want to run it against a set of databases rather than every database.
EXEC sp_msforeachdb '
IF ''?'' IN ( ''db1'', ''db2'', ''db3'', ''db4'' )
SELECT [ABC], [123]
FROM [?].[dbo].[tabledata]
WHERE FieldName = ''QWERTY'''
August 13, 2009 at 2:14 pm
Thanks for pointing me in the right direction. With a bit of research I came up with the following
exec sp_MSforeachdb 'select field_a, field_b from [?].[dbo].[tblname] where DB_NAME() NOT IN(''master'', ''tempdb'', ''model'', ''msdb'') and field_c = ''1'''
However it starts to produce the results I need but then errors out with the following
Msg 208, Level 16, State 1, Line 1
Invalid object name 'master.dbo.tblname'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'tempdb.dbo.tblname'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'model.dbo.tblname'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'msdb.dbo.tblname'.
I thought by using the DB_NAME() NOT IN would keep the query from looking at those particular tables
August 13, 2009 at 3:25 pm
No, the query has to be compiled and it will find out that the table doesn't exist before it runs the query and evaluates the WHERE clause.
You have to do it the way I showed you, where the IF statement tests the database names before it tries to run the SELECT.
August 13, 2009 at 3:53 pm
Thank you... worked like a charm
this is what I ended up using for those who wish to know
exec sp_MSforeachdb 'if ''?'' NOT IN(''master'', ''tempdb'', ''model'', ''msdb'')select field_a, field_b from [?].[dbo].[tblname] where and field_c = ''1'''
August 14, 2009 at 6:56 am
Hey thanks Scott. This can be useful in many ways.
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply