Utilizando e personalizando o uso da "sp_msforeachdb".
17 Jun 2011 · Database Administration, Tech
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.