Executando um comando em todos os databases da instância no SQL Server

Fala pessoal!
Tudo tranquilo, né ?

Hoje vou mostrar a vocês como executar uma query em todos os databases de uma instância SQL Server. Muitas vezes isso é útil para criação de rotinas de monitoramento de espaço, rodar um checkdb em todos os bancos e muitas outras necessidades do dia-a-dia.

Para realizar essa atividade existem várias formas diferentes de programar, mas vou abordar duas das mais utilizadas

sp_MSforeachdb
A procedure sp_MSforeachdb já vem por padrão no SQL Server, mas não possui documentação oficial pela Microsoft até o momento e por isso, muita gente não sabe da sua existência ou como utilizá-la. Um dos detalhes mais importantes em sua utilização, é o wildcart ?, que irá receber o nome do database que está sendo iterado pela Stored Procedure sp_MSforeachdb.

Vamos a um exemplo bem básico que vai apenas exibir o nome de cada database:

EXEC master.sys.sp_MSforeachdb '
USE [?]

SELECT DB_NAME()'

Exibindo os datafiles de cada database

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

Exibindo os datafiles de cada database que contenha a palavra “Cobranca”

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

Exibindo o nome dos databases, schemas e tabelas que começam com Cliente na sua instância

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'

Utilizando views de catálogo do SQL Server
A procedure de sistema sp_MSforeachdb é muito útil no dia-a-dia do DBA e bem fácil de ser utilizada, mas já vi alguns casos onde o banco ignoraalguns databases durante a iteração quando há uma sobrecarga muito grande no servidor. Por isso, vou mostrar uma outra abordagem para atender essa necessidade.

Exemplo 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

Exemplo 2:
Após criar a SP abaixo, retorna o nome de todos os databases e os grava numa tabela temporária

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()'

Retorna o nome de todos os databases que contenham “Cobranca” no nome

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

Retorna o nome de todos os databases que NÃO contenham “Cobranca” no nome

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

Código da Stored Procedure 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