Procedures não documentadas do SQL Server

Ao se deparar com as situações de se rodar um comando ou executar uma atividade em todos os bancos de dados ou em todas as tabelas, você pode considerar a utilização das procedures não documentadas do SQL Server. Essas procedures executam atividades em loop através dos bancos ou tabelas.

Temos a seguinte situação: Preciso executar o comando CHECKDB em todos os meus bancos de forma automática.

Se não houvesse essas procedures poderíamos resolver esse problema criando um loop através da consultar a view para mostrar os bancos ou montar um script. No primeiro caso, a chance de você não obter uma performance aceitável é alta pois os script são otimizados para uma maior performance e o segundo ficaria preso a quantidade de databases no ato de criação do script, ou seja ao incluir ou dropar um banco, ou mesmo criar um banco de homologação ou desenvolvimento com um nome diferente você já teria que adaptar seu script.

Segue abaixo a sintaxe dos comandos:

sp_MSforeachdb

sp_MSforeachdb @command1, @replacechar,
@command2,
@command3, @precommand, @postcommand

Na qual:

Parametro	Descrição	Tipo
@command1	Primeiro comando que será executado em cada base de dados	Nvarchar(2000)
@replacechar	Representa o caractere que será representado pelo nome do banco de dados	Nvarchar(1)
@command2	Segundo comando que será executado em cada base de dados	Nvarchar(2000)
@command3	Terçeiro comando que será executado em cada base de dados	Nvarchar(2000)
@precommand	Comando que será executado antes dos comandos	Nvarchar(2000)
@postcommand	Comando que será executado após os comandos enviados	Nvarchar(2000)

 

Segue um exemplo utilizando a procedure sp_MSforeachdb:

sp_MSforeachdb @command1='DBCC checkdb(''?'')'

Verificando o comando acima, você pode notar que eu não coloquei o parâmetro replacechar. Esse comando não foi emitido pois o padrão para o replacechar é o caractere "?".

sp_MSforeachtable

sp_MSforeachdb @command1, @replacechar,
@command2,
@command3, @whereand,
@precommand, @postcommand

Na qual:

Parametro	Descrição	Tipo
@command1	Primeiro comando que será executado em cada base de dados	Nvarchar(2000)
@replacechar	Representa o caractere que será representado pelo nome do banco de dados	Nvarchar(1)
@command2	Segundo comando que será executado em cada base de dados	Nvarchar(2000)
@command3	Terçeiro comando que será executado em cada base de dados	Nvarchar(2000)
@whereand	Campo para adicionar informações sobre o objeto, por exemplo constraints.	Nvarchar(2000)
@precommand	Comando que será executado antes dos comandos	Nvarchar(2000)
@postcommand	Comando que será executado após os comandos enviados	Nvarchar(2000)

 

Segue um exemplo utilizando a procedure sp_MSforeachtable:

sp_MSforeachtable @command1='select * from ?'

Segue abaixo mais alguns scripts utilizando as procedures acima:

Diversos:

--Criando uma procedure em todos os databases
sp_MSforeachdb 'USE ? EXEC (''CREATE PROCEDURE DBA_BRASIL AS BEGIN SELECT 1 END'')'
sp_MSforeachdb 'USE ? DROP PROCEDURE DBA_BRASIL'

--Verificando as bases de dados no server
EXEC sp_MSForEachDB 'Use ?; SELECT DB_NAME()'

--Verificando os datafiles de cada database
EXEC sp_MSForEachDB 'Use [?]; EXEC sp_helpfile'

--Fazer shrink dos databases
EXEC sp_MSForEachDB 'DBCC SHRINKDATABASE (''?'' , 0)'

Verificando espaços utilizados:

-- Verificando os espaços utilizados pelas tabelas:
CREATE TABLE Plano_Capacidade (
TableName sysname,
Rows int,
Reserved varchar(255),
Data varchar(255),
Index_Size varchar(255),
Unused varchar(255)
)

INSERT INTO Plano_Capacidade
EXEC sp_MSForEachTable 'EXEC sp_SpaceUsed ''?'''

SELECT * FROM Plano_Capacidade

Atualizando estatísticas dos bancos de dados:

EXEC sp_MSForEachTable 'UPDATE STATISTICS ?'

 

Qualquer duvida ou script me envie para que possamos trocar algumas informações. Obrigado e até a próxima.