Hey guys!
Everything is calm, right?

Today I'm going to show you how to run a query on all databases in a SQL Server instance. This is often useful for creating space monitoring routines, running a checkdb on all banks and many other day-to-day needs.

To carry out this activity there are several different ways of programming, but I will address two of the most used

sp_MSforeachdb
The sp_MSforeachdb procedure already comes by default in SQL Server, but it does not have official documentation from Microsoft at the moment and therefore, many people do not know about its existence or how to use it. One of the most important details in its use is the wildcart ?, which will receive the name of the database that is being iterated by the Stored Procedure sp_MSforeachdb.

Let's look at a very basic example that will just display the name of each database:

EXEC master.sys.sp_MSforeachdb '
USE [?]

SELECT DB_NAME()'

Displaying the datafiles of each database

EXEC master.sys.sp_MSforeachdb 'SELECT * FROM [?].sys.database_files'

Displaying the datafiles of each database that contains the word “Cobranca”

EXEC master.sys.sp_MSforeachdb '
IF (''[?]'' LIKE ''%Cobranca%'')
    SELECT * FROM [?].sys.database_files
'

Displaying the name of databases, schemas and tables that begin with Customer in your instance

EXEC master.sys.sp_MSforeachdb '
USE [?];

IF((SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''Cliente'') > 0)
BEGIN
    SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE ''Cliente%''
END'

Using SQL Server catalog views
The sp_MSforeachdb system procedure is very useful in a DBA's day-to-day life and is very easy to use, but I have seen some cases where the database ignores some databases during the iteration when there is a very large overload on the server. Therefore, I will show another approach to meet this need.

Example 1:

IF (OBJECT_ID('tempdb..#Databases') IS NOT NULL) DROP TABLE #Databases
SELECT [name], [dbid], IDENTITY(INT, 1, 1) AS Ordem
INTO #Databases
FROM sys.sysdatabases	WITH(NOLOCK)
ORDER BY name


DECLARE
    @Qt_Databases INT = (SELECT COUNT(*) FROM #Databases),
    @Contador INT = 1,
    @Ds_Database SYSNAME,
    @Ds_Query VARCHAR(MAX) = 'SELECT * FROM sys.database_files',
    @Cmd VARCHAR(MAX)

	
WHILE(@Contador <= @Qt_Databases)
BEGIN
		
    SELECT @Ds_Database = name
    FROM #Databases
    WHERE Ordem = @Contador

    SET @Cmd = 'USE [' + @Ds_Database + ']; ' + CHAR(10) + @Ds_Query
    EXEC(@Cmd)
        
    SET @Contador = @Contador + 1

END

Example 2:
After creating the SP below, it returns the name of all databases and writes them to a temporary table

IF (OBJECT_ID('tempdb..#Dbs') IS NOT NULL) DROP TABLE #Dbs
CREATE TABLE #Dbs (
    Nome SYSNAME
)

INSERT INTO #Dbs
EXEC dbo.stpExecuta_Query_Databases
    @Ds_Query = 'SELECT DB_NAME()'

Returns the name of all databases that contain “Cobranca” in the name

EXEC dbo.stpExecuta_Query_Databases
	@Ds_Query = 'SELECT DB_NAME()',
	@Ds_Incluir_Database = '%Cobranca%'

Returns the name of all databases that DO NOT contain “Cobranca” in the name

EXEC dbo.stpExecuta_Query_Databases
	@Ds_Query = 'SELECT DB_NAME()',
	@Ds_Excluir_Database = '%Cobranca%'

Stored Procedure code stpExecuta_Query_Databases:

CREATE PROCEDURE dbo.stpExecuta_Query_Databases (
    @Ds_Query VARCHAR(MAX),
    @Ds_Incluir_Database VARCHAR(MAX) = NULL,
    @Ds_Excluir_Database VARCHAR(MAX) = NULL
)
AS BEGIN

    IF (OBJECT_ID('tempdb..#Databases') IS NOT NULL) DROP TABLE #Databases
    CREATE TABLE #Databases (
        [name] SYSNAME,
        [database_id] INT,
        [Ordem] INT IDENTITY(1, 1)
    )

    IF (@Ds_Incluir_Database IS NULL AND @Ds_Excluir_Database IS NULL)
    BEGIN

        INSERT INTO #Databases
        SELECT [name], [database_id]
        FROM sys.databases	WITH(NOLOCK)
        WHERE state_desc = 'ONLINE'
        ORDER BY name

    END
    ELSE BEGIN
        
        IF (@Ds_Incluir_Database IS NOT NULL)
        BEGIN

            INSERT INTO #Databases
            SELECT [name], [database_id]
            FROM sys.databases	WITH(NOLOCK)
            WHERE [name] LIKE (@Ds_Incluir_Database)
            AND state_desc = 'ONLINE'
            ORDER BY name

        END
        ELSE BEGIN

            INSERT INTO #Databases
            SELECT [name], [database_id]
            FROM sys.databases	WITH(NOLOCK)
            WHERE [name] NOT LIKE (@Ds_Excluir_Database)
            AND state_desc = 'ONLINE'
            ORDER BY name

        END

    END	


    DECLARE
        @Qt_Databases INT = (SELECT COUNT(*) FROM #Databases),
        @Contador INT = 1,
        @Ds_Database SYSNAME,
        @Cmd VARCHAR(MAX)


    WHILE(@Contador <= @Qt_Databases)
    BEGIN
        
        SELECT @Ds_Database = name
        FROM #Databases
        WHERE Ordem = @Contador

        SET @Cmd = 'USE [' + @Ds_Database + ']; ' + CHAR(10) + @Ds_Query
        EXEC(@Cmd)
        
        SET @Contador = @Contador + 1

    END


END