Hey guys!
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:
|
1 2 3 4 |
EXEC master.sys.sp_MSforeachdb ' USE [?] SELECT DB_NAME()' |
Exibindo os datafiles de cada database
|
1 |
EXEC master.sys.sp_MSforeachdb 'SELECT * FROM [?].sys.database_files' |
Exibindo os datafiles de cada database que contenha a palavra “Cobranca”
|
1 2 3 4 |
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
|
1 2 3 4 5 6 7 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
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
|
1 2 3 4 5 6 7 8 |
IF (OBJECT_ID('tempdb..#Dbs') IS NOT NULL) DROP TABLE #Dbs CREATE TABLE #Dbs ( Name 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
|
1 2 3 |
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
|
1 2 3 |
EXEC dbo.stpExecuta_Query_Databases @Ds_Query = 'SELECT DB_NAME()', @Ds_Excluir_Database = '%Cobranca%' |
Código da Stored Procedure stpExecuta_Query_Databases:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
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 |
Portuguese 
English
Boa tarde!
Você tem alguma referência de que a sp_MSforeachdb ignora alguns bancos conforme mencionou? É que eu tenho esse problema aqui na empresa, mas se eu for só dizer que há um problema vão querer que eu dê evidênvcias.
Obrigado.
Boa tarde, um simples if:
EXEC SP_MSFOREACHDB ‘USE [?]
—
IF (DB_NAME() IN (”BASE1”, ”BASE2”)) BEGIN
— SEU CÓDIGO AQUI
END
Dirceu! Fantástica essa SP e o seu artigo! Eu não a conhecia e realmente ela me poupou muito tempo e código para um levantamento simples que eu precisava fazer de quantidade de PK e FK de 25 databases para comparação básica de estrutura num trabalho pontual.
Muito obrigada pela sua contribuição!!! Parabéns pelo excelente artigo!
Obrigado pelo feedback, Herica 🙂
Parabéns respondeu atá as dúvidas que eu ia ter