Using Dynamic SQL to switch 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 2005 Forums

Transact-SQL (2005)

Using Dynamic SQL to switch databases

Author

Topic

btownzen
Starting Member

2 Posts

Posted - 2008-11-08 : 11:16:28

How can I use Dynamic SQL to make it work for each database identified by my admin table (name = DPS)?

Here is what I have:
Database: DPS (Admin Database)
Table: Main (Main Admin Table)
Field: Database Name (Contains the name of a project database)

I want to loop through each record in the DPS.dbo.Main table and for each value in the “Database Name” field I want to go to the database identified and read the “Main” table from that database.

Current Code:
USE DPS;
DECLARE @SQL NVARCHAR(1000);
DECLARE @DBName NVARCHAR(1000);
DECLARE @TBName NVARCHAR(1000);

SET @DBName = (SELECT [Database Name] FROM MAIN WHERE [Database Name] = 'Apples')
SET @TBName = @DBName + '.dbo.Main'
-- Now the value of @TBName = "Apples.dbo.Main"

SET @SQL = 'SELECT docsource, COUNT(*) AS [DocTot], SUM(NUMOFPAGES) AS [PageTot] FROM ' + @TBName + ' WHERE (Stage BETWEEN 5 AND 6)' + ' GROUP BY docsource'
-- Now the value of @SelectString = “SELECT docsource, COUNT(*) AS
[DocTot], SUM(NUMOFPAGES) AS [PageTot] FROM Apples.dbo.Main WHERE (stage BETWEEN 5 AND 6) GROUP BY docsource”

EXEC(@SQL)

The code above works but only for the database name specified, in this case: “Apples”. How do I structure the query to loop through all my databases?

I would really appreciate your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-08 : 11:32:18

just use system stored proce sp_Msforeachdb

EXEC sp_Msforeachdb 'SELECT docsource, COUNT(*) AS [DocTot], SUM(NUMOFPAGES) AS [PageTot] FROM ?.dbo.Main WHERE Stage BETWEEN 5 AND 6 GROUP BY docsource'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-10 : 01:23:45

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/05/13/simulating-undocumented-procedures.aspx

Madhivanan

Failing to plan is Planning to fail

btownzen
Starting Member

2 Posts

Posted - 2008-11-10 : 14:22:41

Fantastic! Thank you both.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources