Utilizando e personalizando o uso da “sp_msforeachdb”.

Utilizando e personalizando o uso da “sp_msforeachdb”.
Rate this post

Existem diversas procedures “não documentadas” no SQL SERVER que nos dão uma super ajuda em diversas tarefas cotidianas, tanto para a administração de uma instância, quanto para desenvolvimento em geral. A vantagem da utilização destas procedures não documentadas, é que elas nos permitem ganhos de produtividade em atividades que necessitam ser executadas em diversos objetos dentro da instância, entre eles: Databases (sp_msforeachDB), Tables (sp_msforeachTable) e outros.


Abaixo, temos uma explicação bastante objetiva do uso da procedure não documentada “sp_msforeachDB”, que é utilizada para executar um script em todos os databases de uma instância.

A sintaxe básica:

exec sp_msforeachdb @command1 = N‘<command>;’,

                                 @command2 = N‘<command>’,

                                 @command3 = N‘<command>’

ou então, opcionamente pode-se utilizar da seguinte maneira:

exec sp_msforeachdb @command1 = N‘<command>;<command>;<command>;’

Esta procedure também aceita alguns parâmetros a serem passados para execução. Destes o único parâmetro obrigatório é o “@command1”, os demais são todos opcionais.

  • @command1 = Primeiro comando a ser executado pela procedure.
  • @command2 = Segundo comando a ser executado pela procedure.
  • @command3 = Terceiro comando a ser executado pela procedure.
  • @replacechar = Caractere  ser utilizado para indicar qual database está sedo utilizado em cada ciclo de execução. A omissão deste parâmetro implica que será utilizado o caractere default “?”.
  • @precommand = Comando a se executado antes da execução do primeiro comando declarado.
  • @poscommand = Comando a se executado após a execução do último comando declarado.

Exemplos práticos da utilização desta poderosa procedure.

Exemplo 1: Você precisa verificar o nome lógico, diretório de armazenamento e tamanho dos arquivos de dados e log do database “master” da sua instância, para isto, basta executar o comando abaixo na instância do SQL SERVER.

use [master];
select db_name() as DBNAME;
exec sp_helpfile;

Agora, uma outra situação, é a necessidade desta mesma verificação em todos os datafiles e logfiles de todos os databases de uma instânca, neste caso, pode-se utilizar o mesmo script utilizado anteriormente, com algumas pequenas adaptações, para rápida execução desta tarefa. O novo script com a utilização da sp_msforeachdb, ficaria conforme exemplo abaixo:

exec sp_msforeachdb @command1 = N    use ?
                                                               select ”?” as DBNAME
                                                               exec sp_helpfile’

Exemplo 2: Você precisa executar um verificação e listar os usuários que estão associados à database role “db_owner” em um determinado database. Basta executar o script a seguir:

use [Teste_Monitoring];
select db_name() as DBNAME
exec sp_helprolemember ‘db_owner’

Para execução a mesma atividade, em todos os databases da instância, basta “colar” o script para um único database dentro do parametro da procedure sp_msforeachdb e fazer as e devidas adaptações com apóstrofos e caracteres de indicação do database a ser utilizado, neste caso o “?
exec sp_msforeachdb @command1= N‘    use [?]
                                                              select ”?” as DBNAME
                                                              exec sp_helprolemember ”db_owner”’

Resumido, para qualquer necessidade em que precisa-se executar um script em todos os databases de uma instância, é importante e bastante útil primeiro criar o script para um único database e em seguida adaptar este script para a procedure não documentada sp_msforeachdb.

Espero que o post seja útil. Dúvidas, elogios, crítcas e sugestões esteja à vontade para comentar ou entrar em contato através dos dados na seção “Sobre Edvaldo Castro” do Blog.

5 thoughts on “Utilizando e personalizando o uso da “sp_msforeachdb”.

  1. Parabéns pelo Ótimo Post, blogs como esses que nos ajudam a ampliar nossos conhecimentos e lidar com as tarefas cotidianas de nossa profissão.

    Hoje graças a esse post conseguir efetuar meu trabalho com mais agilidade adaptando a sp_msforeachdb para me retornar os users e todas as roles de todos os bancos.

    Muito Obrigado.

Deixe uma resposta

dba consultor consultoria consulting sql server always on alta disponibilidade HA HADR

dba consultor consultoria consulting sql server always on alta disponibilidade HA HADR

%d blogueiros gostam disto: