Utilizando e personalizando o uso da “sp_msforeachdb”.

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.