Pull Data from Multiple Databases

At one point or another, anyone with database access has found themselves tasked to pull XYZ data from the database. If there is only one database that contains this data, this isn’t a huge request, but if there are multiple databases with the same schema and different data, this can get tedious. Today we are going to walk through a simple yet useful way to pull data from all databases on a single database server.

To accomplish this, we are going to be using an undocumented stored procedure, sp_MSforeachdb, available in SQL Server. As the name implies, you are not going to find any documentation about this in MSDN and Microsoft can change or remove this procedure at any time. The fact that this procedure is undocumented means it is not suitable for production code, this is better suited for one off queries or data requests.

Despite the fact that you will not find any documentation on MSDN does not mean that you cannot find any information about it. A google search will give you a number of sites with detailed information on how you can use this procedure. Today we are only going to cover the most basic use that will most likely be suitable for 90% of use cases.

An example call to this query is below, you execute the proc like any other and pass in the SQL you want run, in this case we are passing in a variable. One thing to note, this procedure takes in a variable for the character that will be replaced with each database name. The default is “?” so any instance of “?” in the @sql variable is going to be replaced with each database name on the server then the SQL will be executed. You will have to include a use statement to make sure you are running the statement on each database.

declare @sql nvarchar(2000) = N'use [?]
                                select db_name()';

exec sp_MSforeachdb @sql;

in the example above, we are passing in a simple query to first switch databases then return the name of each database as an example of how this query works.

For the remainder of this article we are going to assume we are tasked with pulling all data from the CUSTOMER table. We know that we have multiple sales databases on a single database server each having multiple records in this table.

CREATE TABLE [dbo].[CUSTOMER](
    [CustomerID] [int] NULL,
    [CustomerName] [varchar](100) NULL,
    [CustomerAddress] [varchar](100) NULL,
    [CustomerCity] [varchar](100) NULL,
    [CustomerState] [varchar](100) NULL,
    [CustomerZipCode] [varchar](100) NULL,
    [CustomerCountry] [varchar](100) NULL
) ON [PRIMARY]
GO

The first things we are going to want to do is create a temp table with all columns we want to pull from each database. A temp table is required because otherwise this procedure is going to execute the SQL on each database and have separate result sets for each, like in our first example, we want one result set.

create table #customers (databasename sysname, customerName varchar(100), customerAddress varchar(100), customerCity varchar(100),
                         customerState varchar(100), customerZipCode varchar(100), customerCountry varchar(100));

Next, we are going to declare the variable that we will assign our SQL statement to. Remember that “?” is the default variable that will be replaced with each database name so at the beginning we have “use [?]” so that we are going to each database. Notice that we are first checking for the existence of the table we are pulling from, this is because sp_MSforeachdb is going to run on all databases, including system databases, which will most likely not have the user table you are looking for.

declare @sql nvarchar(2000);
set @sql = N'
use [?]; 
if exists (select * from sys.tables where name = ''CUSTOMER'') begin
    insert into #customers
    select db_name(), customerName, customerAddress, customerCity, customerState, CustomerZipCode, customerCountry
    from   dbo.customer
end';

Finally, we will call the procedure and return our results. As stated earlier, we are only covering the simplest way to use this procedure so all we are passing in is the SQL statement we want executed. After execution, we return the results that we have inserted into the temp table, then dropping the temp table.

exec sp_MSforeachdb @sql

select * from #customers;

drop table #customers;

By putting all of this together you will get the final statement to pull all the data which will give you the below results.

create table #customers (databasename sysname, customerName varchar(100), customerAddress varchar(100), customerCity varchar(100),
                         customerState varchar(100), customerZipCode varchar(100), customerCountry varchar(100));

declare @sql nvarchar(2000);
set @sql = N'
use [?]; 
if exists (select * from sys.tables where name = ''CUSTOMER'') begin
	insert into #customers
    select db_name(), customerName, customerAddress, customerCity, customerState, CustomerZipCode, customerCountry
    from   dbo.customer
end';
exec sp_MSforeachdb @sql

select * from #customers;

drop table #customers;

As we have seen here, sp_MSforeachdb is a powerful tool to have when you are tasked with pulling data from all databases. This example showed pulling data from a user table but you can just as easily pull data from a system table on all databases. The only caution here is that you will not want to build production systems around this procedure.

Share this:

Like this: