March 4, 2005 at 2:29 pm
I am working on a query that does a select from one database to get the names of other databases that I need to run a specific query against. I have not been able to get the "Use DATABASENAME" statement to work correctly.
Any input will be greatly appreciated.
/**** Start Here ***/
Use MainDatabase
DECLARE @DBName VARCHAR(50)
Set @DBName = ' '
While @DBName IS NOT NULL
Begin
Select @DBName = name
from databases
If @DBName IS NOT NULL
Begin
Use @DBName
select * from mysites,mywebs
where mywebs.siteid = mysites.id
and id = 1
End
END
March 5, 2005 at 1:53 am
It seems like you want to execute this query aginast several databases. Is this the case?
Assuming so, you have a few options:
1. Use a WHILE loop or a cursor to go through each entry in the databases table. Then execute a dynamic query within the loop:
EXEC('use ' + @dbName + 'select * from mysites, mywebs where ..... ')
2. Use the undocumented function sp_msforeachdb to loop through the databases for you. This uses a cursor against all dbs on the server. You can check if they are in your table and if so, execute the query. The trick here is to use a placeholder '?' to represent the db name:
sp_msforeachdb 'IF EXISTS (select * from Maindatabase.dbo.databases where name = ''?'') select * from [?].dbo.mysites, [?].dbo.mywebs where .... '
Here, the ''?'' are escaped single quotes, and the brackets are used in case your database names have non alphanum characters.
If you need more detail, let me know.
Hope this helps,
Scott Thornburg
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply