Query All Databases Across Multiple SQL Servers and Store Results in Table

By: Jim Evans   |   Updated: 2023-03-27   |   Comments (2)   |   Related: More > Central Management Servers

Problem

Often as a SQL Server DBA, you may need to pull information from all databases across all your SQL Servers. What is an easy way to do this without manually connecting to each SQL Server and each database directly? Also, how can I easily consolidate the results? What are some examples of when a DBA might need to pull data from every database?

Solution

This tip will show how to use Registered SQL Servers in SQL Server Management Studio (SSMS) to run a query across all servers. Then we will use the SQL Server system stored procedure sp_msForEachDB to query all databases on each SQL Server and consolidate the results.

Set Up Registered SQL Server in SQL Server Management Studio

To begin, we will use SSMS to set up a Registered Server Group and add 3 Registered Servers to the Group. You can follow this example but register your list of servers.

Create a Registered Server Group

From SSMS, click View in the top menu, then select Registered Servers.

Right-click the Local Server Groups and select New Server Group.

Name the Server Group and provide a description (optional). Click OK.

In Registered Servers under Local Server Groups, you can now see your newly created group named "A Server Group." The image below also shows other groups that I previously created.

Add Servers to the SQL Server Group

Right-click "A Server Group" and select New Server Registration.

Enter the Server name and Authentication method. If you select SQL Server Authentication, enter the user name and password and check the box for Remember Password. This works best with Windows Authentication. You can modify the Registered Server Name and provide a customer name. Also, it may be helpful to enter a Registered Server Description.

Click Save. Repeat this step for each server you want to register and add to the Group.

Now go back to the Registered Servers and expand "A Server Group" to see all your servers.

Run a Query Across the Registered Servers

In this step, we will show how to run a query across all the Servers in the Server Group.

Right-click "A Server Group" and select New Query. A new query window will open, showing a connection to all the servers in the Server Group.

Pass the following simple query in the query window. This will run against the master system database on each Registered Server.

SELECT COUNT(*)
FROM sys.sysdatabases;
GO

Run the query, and you will see that the Server Group query was run on all the servers registered in the Group. By default, the Registered Server names are appended to the results so you can see which rows are from what server. In this case, I have 12 databases on each of my Registered Servers.

Run a Query Against All Databases on Each Registered Server

We will use a system store procedure to run a query across all databases on a SQL Server: sp_msForEachDB. The sp_msforeachdb system stored procedure is a great tool for gathering info from all databases on a single server. It takes a query as a parameter and loops through all the databases running the query on each. The sp_msforeachdb stored procedure uses a "?" placeholder for substituting in the database name when looping through all the databases on a SQL Server. However, for this tip, I will show the use of sp_msforeachdb running against all the databases across all the servers in the Registration Server Group.

Prepare the Query to Pass into sp_msForEachDB

For this example, we will retrieve a list of all the tables and views and their metadata from the system view Information_schema.Tables. We will pass the query to sp_msForEachDB as a parameter to run the query across all databases. We will use [?] to change databases and to run the query in each database. Note: I used square brackets if any database has a space in the name. Also, if the query being passed has single quotes, use two single quotes, a second to escape the embedded quote.

Below are three options for calling sp_ msForEachDB. Often, I use the first option with a Use [database] statement. Option 2 uses a three-part naming convention, where the third option includes a method for excluding system databases. Note: In option 3, I include single quotes.

--Option 1: USE [?]
exec sp_msforeachdb 'USE [?] SELECT * FROM Information_schema.Tables;'
GO

--Option 2: 3 Part Name
exec sp_msforeachdb 'SELECT * FROM [?].Information_schema.Tables;'
GO

--Option 3: exclude system DBs
exec sp_msforeachdb 'IF ''?'' not in (''master'',''model'',''msdb'',''tempdb'') exec [?].dbo.sp_spaceused;'
GO

Execute the Query Across the Registered Servers

Next, run the Option 1 query across the Registered Servers. Use the same query window used before that has a connection to all the servers. Review your results. The demo results below show all tables and views across all the databases, but the results have been sorted into several different result sets.

Consolidating the Results

Next, we will take the above pieces using Registered Servers and sp_msForEachDb, add more complexity, and combine it into a single result set that can be saved or copied and pasted, perhaps into an email or Excel.

Create a Table to Consolidate the Results from Each Database

To consolidate the results into a single result grid, we will create a temporary table to insert the results from each database as we loop through them. To create the table, a shortcut technique using SELECT can be applied to return no rows, creating the empty table in the structure consistent with the query result datatypes. The modified query is below.

SELECT * 
INTO #TempTable
FROM Information_schema.Tables
WHERE 1 = 2; --< Never, so create a table in the structure I want with No Rows!
GO

Run the Query Across Multiple Servers Consolidating the Results

The script below includes our table creation using the shortcut technique, a call to sp_msForEachDB that loops through each database, retrieving information about each table and view in that database. These results are inserted into the new table, #TempTable, and a SELECT of the consolidated results from each database. All this is being run on each server in the Registered Server Group. Since the final SELECT is the same on each Server, the results are collected into a single result grid.

SELECT * 
INTO #TempTable
FROM Information_schema.Tables
WHERE 1 = 2; --< Never, so create a table in the structure I want with No Rows!
 
INSERT INTO #TempTable
exec sp_msforeachdb 'USE [?] SELECT * FROM Information_schema.Tables;'
 
SELECT * FROM #TempTable;

DROP Table #TempTable;
GO

Run the Query Across Multiple Servers Consolidating the Results

The screenshot below shows that the query above returns all the results from all servers in the Registered Server Group, from each database, into a consolidated result grid.

When Would a DBA Need to Pull Data from Multiple Databases Across Servers?

Below are some examples of when to query all databases across multiple SQL Servers:

• Get all database users in the db_owner group in all databases for audit purposes.
• Get a list of tables per database across all servers to help catalog databases.
• Get a list of table column names and datatypes per database across all servers to help identify HIPAA or PHI data.
• Pull database information on data files and storage.
• Pull all database compatibility levels.
• Push out security to all databases like a global Read Only user or a new sys admin account.

These are a few examples of how you might use Registered Servers and sp_msForEachDB to run queries across multiple databases.

MultiServer Results Configuration

Last, you should be aware of the MultiServer Results configuration options. In SSMS, on the top menu, select Tools, then Option. Drill down to Query Results, SQL Server, Multiserver Results. Review the options below that control the multiserver results.

Wrap Up

I hope you find queries across Registered Server Groups and the sp_msForEachDB techniques useful both in combination and individually. These powerful tools help save time, produce quick results, and help you work smarter, not harder. Note: sp_msforeachdb does not work across Azure DB databases as they are Self-Contained, and the "Use <database name>" statement does not work in these databases. Look for my next tip about the options to pull data from many AzureDB instances.

Next Steps

• More on: Combine Multiple SQL Server Query Results with Registered Servers
• Learn a different take on Registered Servers: Run Same Command on All SQL Server Databases Without Cursors
• Other SSMS Time Saving Tips
• Learn more about SQL Security Audits
• Learn how to Grant User Access to All SQL Server Databases

About the author

Jim Evans is an IT Manager currently for Crowe who has managed DBA, Developer, BI and Data Management teams for over 20 years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips