Traverse all Transaction Databases of a Transaction DB server to execute a query to get data for all databases

Suppose we have a client’s sales data country wise, which is stored in the database separately for each Country as:

So for our reporting purpose we need to fetch the data from Sales tables of each database to show the data for all countries in one resultset. For this I have followed these steps:

First I fetched list of all database name from the sys.databases table or if we have a table which containing all countries information that would be great because in future if any new database will be added for new country then this table will be provided that country’s information also so below is the table I have for this:

CountryName	DataBaseName
India	India
Spain	Spain
UK	UK
US	USA

 I have used this table to get unique databases name to traverse all of them.

Below query, we can use to get list of all databases from a DB server also:

SELECT name as DBName, ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY name) as RNo

FROM sys.databases

Where name NOT IN ('master','model','msdb','ReportServer','ReportServerTempDB','tempdb')

ORDER BY name
 

In this Query I am including row number for each one row based on ordering of Dabases name, which I will use to set loop’s break condition.
In th below code first I am trying to drop temp tables if they are existing and after that I am fetching all the databases name and storing them in #DBName table and than cretating an another temp table to store the sales data for combining at the time of looping and I am using a while loop to traverse until the max row number does not exceed and finally executing temp table #SalesData which is used to storing data at the time of looping through each database.

 

GO

IF OBJECT_ID('tempdb.[dbo].[#DBName]') IS NOT NULL DROP TABLE [dbo].[#DBName]

IF OBJECT_ID('tempdb.[dbo].[#SalesData]') IS NOT NULL DROP TABLE [dbo].[#SalesData]

 

SELECT name as DBName, ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY name) as RNo

INTO #DBName

FROM sys.databases

Where name NOT IN ('master','model','msdb','ReportServer','ReportServerTempDB','tempdb')

ORDER BY name

 

/* Create a temp table or table variable with all columns which we need to store data for final resultset */

Create Table #SalesData (ID varchar(50),Country varchar(50))

 

Declare @DB varchar(50)

Declare @i int

 

Set @i=1

While(@i<=(Select Max(RNo ) From #DBName))

Begin

      set @DB=(Select Name from #DBName where RNo= @i)

 

Insert Into #SalesData

            Exec ('Select ID,'''+@DB+''' from '+@DB+'.dbo.Sales')

 

      Set @i=@i+1

            End

 SELECT * FROM #SalesData

Go