sp_MSforeachdb: only include results from databases with results

Filtering sp_MSforeachdb Results to Include Only Databases with Data

Learn how to effectively use sp_MSforeachdb in SQL Server to execute commands across multiple databases, while intelligently filtering results to show only those databases that return actual data, avoiding empty result sets.

The sp_MSforeachdb stored procedure is an undocumented but widely used utility in SQL Server for executing a T-SQL command against every database on an instance. While incredibly powerful, a common challenge arises when the command executed returns no results for many databases, leading to a cluttered output with numerous empty result sets. This article will guide you through techniques to refine your sp_MSforeachdb usage, ensuring that your final output only includes data from databases where your query actually found something.

Understanding the Challenge with sp_MSforeachdb

By default, sp_MSforeachdb iterates through all databases (or a filtered subset if specified) and executes the provided command. If your command is a SELECT statement, it will produce a result set for each database. If a database contains no data relevant to your query, an empty result set is still returned. This can make analyzing the output difficult, especially when dealing with hundreds of databases where only a few might have relevant information.

Consider a scenario where you're looking for a specific table or data within a table across all databases. Running a simple SELECT might return hundreds of empty grids in SQL Server Management Studio (SSMS) or a large, sparse output if redirected to text. Our goal is to suppress these empty results and only display the meaningful ones.

flowchart TD
    A[Start sp_MSforeachdb] --> B{Iterate Each Database}
    B --> C[Execute Command in Current DB]
    C --> D{Are Results Returned?}
    D -->|Yes| E[Collect Results]
    D -->|No| F[Discard Empty Results]
    E --> G{All Databases Processed?}
    F --> G
    G -->|No| B
    G -->|Yes| H[Display Collected Results]
    H --> I[End]

Flowchart illustrating the process of filtering sp_MSforeachdb results.

Method 1: Using Temporary Tables for Aggregation

One of the most robust ways to achieve filtered results is to use a temporary table to collect data only from databases that produce results. This method involves creating a global temporary table (or a local one if the scope allows) before the sp_MSforeachdb call, inserting results into it within the loop, and then querying the temporary table after the loop completes. This gives you full control over the final output.

CREATE TABLE ##TempResults (
    DatabaseName NVARCHAR(128),
    TableName NVARCHAR(128),
    RowCount INT
);

EXEC sp_MSforeachdb N'USE [?];
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = ''YourTable'')
BEGIN
    INSERT INTO ##TempResults (DatabaseName, TableName, RowCount)
    SELECT DB_NAME(), ''YourTable'', COUNT(*)
    FROM YourTable
    WHERE SomeColumn IS NOT NULL; -- Add your specific WHERE clause here
END;';

SELECT * FROM ##TempResults;

DROP TABLE ##TempResults;

Using a global temporary table to collect and filter results from sp_MSforeachdb.

Method 2: Conditional Execution within the Loop

For simpler queries, you can embed conditional logic directly within the sp_MSforeachdb command string. This approach avoids temporary tables but might be less flexible for complex aggregations or when you need to combine results from multiple queries within a single database. The key is to only execute the SELECT statement if a preceding condition is met, indicating that data exists.

EXEC sp_MSforeachdb N'USE [?];
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = ''YourTable'' AND OBJECTPROPERTY(OBJECT_ID(''YourTable''), ''IsUserTable'') = 1)
BEGIN
    IF EXISTS (SELECT 1 FROM YourTable WHERE SomeColumn IS NOT NULL)
    BEGIN
        SELECT DB_NAME() AS DatabaseName, ''YourTable'' AS TableName, COUNT(*) AS RowCount
        FROM YourTable
        WHERE SomeColumn IS NOT NULL;
    END
END;';

Conditional execution within sp_MSforeachdb to only return results when data is found.

Method 3: Encapsulating Logic in a Stored Procedure

For highly complex scenarios or when you need to reuse the logic, creating a dedicated stored procedure that performs the check and returns results is an excellent strategy. sp_MSforeachdb can then simply execute this stored procedure in each database.

USE master;
GO

CREATE PROCEDURE dbo.usp_GetRelevantTableData
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'YourTable' AND OBJECTPROPERTY(OBJECT_ID('YourTable'), 'IsUserTable') = 1)
    BEGIN
        IF EXISTS (SELECT 1 FROM YourTable WHERE SomeColumn IS NOT NULL)
        BEGIN
            SELECT DB_NAME() AS DatabaseName, 'YourTable' AS TableName, COUNT(*) AS RowCount
            FROM YourTable
            WHERE SomeColumn IS NOT NULL;
        END
    END
END;
GO

-- Now execute it using sp_MSforeachdb
EXEC sp_MSforeachdb N'USE [?]; EXEC dbo.usp_GetRelevantTableData;';

-- Clean up (optional)
DROP PROCEDURE dbo.usp_GetRelevantTableData;
GO

Using a custom stored procedure with sp_MSforeachdb for cleaner, reusable logic.