Script -> Query for multiple databases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Script -> Query for multiple databases

Author

Topic

need_an_answer
Starting Member

1 Post

Posted - 2014-08-01 : 04:19:38

Hi,

i've got the following problem: I want to execute a query in multiple databases on my Server. For every customer that i have, there is a db. Those dbs all have exactly the same table and their names are similar. So there is a db kde_01_Miller, then a kde_02_Mueller and so on ...

I want to execute a query in every one of those dbs.

Here's what i tried:


DECLARE @name VARCHAR(100) -- database name
DECLARE @dothis nvarchar(200)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name like 'kde_0%'
order by name

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0
BEGIN  
       set @dothis = 'use [' + @name + ']'
       exec sp_executesql @dothis

       /* Start query */
       select description from dbo.basicdata
       /* End query */

       FETCH NEXT FROM db_cursor INTO @name  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

The problem is, that the query does not work properly. The use-statement seems not to be working. I get a result for every database i have, but the result is always the same one, dependent on the database, i'm currently doing a query for.

I've also tried the following and it worked: Instead of my while-loop i did this:


WHILE @@FETCH_STATUS = 0
BEGIN  
       set @dothis= 'select description from ' + QUOTENAME(@name) + '.dbo.basicdata'
       exec sp_executesql @dothis

       FETCH NEXT FROM db_cursor INTO @name  
END

But i don't like this way, because you need the quotename(@name) for every table.

How do i make the first example work?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-08-01 : 09:18:02

Try using the sp_MSforeachdb procedure. http://weblogs.sqlteam.com/joew/archive/2008/08/27/60700.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources