Procedures não documentadas sp_MSforeachdb e sp_MSforeachtable

Olá pessoal,

No post de hoje vou falar sobre as procedures não documentadas do SQL Server sp_MSforeachdb e sp_MSforeachtable, elas são muito úteis quando você se deparar com uma atividade, em que você necessite buscar as informações em todos os bancos de dados ou todas as tabelas. Essas procedures executam atividades em loop através dos bancos ou tabelas.

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

Poderíamos fazer um script utilizando views de sistemas e montar um loop, porem podemos ficar preso a quantidade de bancos no ato de criação do script, ou seja ao incluir, dropar ou até mesmo ao alterar o nome de um banco, você teria que lembrar de alterar o script.

Neste caso podemos utilizar a sp_MSforeachdb como o próprio nome sugere, ela é utilizada para executar determinada query para cada base atachada na instância.

A sintaxe do comando sp_MSforeachdb é:

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

onde:

Vejamos o exemplo da situação acima:

EXECUTE master.sys.sp_MSforeachdb'dbcc checkdb(''?'')'

O comando vai executar checkdb em todos os bancos de dados da instância.

Se por ventura precisar excluir um ou mais bancos, podemos também criar filtro.

EXECUTE master.sys.sp_MSforeachdb'if ''?'' NOT IN (''Master'',''MSDB'',''Model'',''Tempdb'') DBCC checkdb(''?'')'

No exemplo o checkdb foi executado em todos os bancos de usuários menos nas bases de sistemas.

A procedure sp_MSforeachtable tem a mesma função, porém ela faz um loop em todas as tabelas de um banco de dados.

A sintaxe para utilizar a procedure sp_MSforeachtable é:

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

onde:

Imagine que temos a seguinte situação: Preciso executar saber o tamanho de cada tabela do meu banco de dados.

EXEC sp_MSforeachtable 'EXEC sp_spaceused ''?'''

Assim como na sp_MSforeachdb também podemos filtrar as tabelas que queremos executar o comando.

 EXEC sp_MSforeachtable @command1 = 'IF ''?'' NOT IN  (''[HR].[Employees]'',''[Production].[Suppliers]'') EXEC sp_spaceused ''?''';

Além das procedures sp_MSforeachdb e sp_MSforeachtable existem outras procedures não documentadas como as da lista abaixo, não existe suporte ou material oficial que ensine a utilizá-la por parte da Microsoft.

sp_checknames
sp_columns_rowset
sp_enumoledbdatasources
sp_fixindex
sp_gettypestring
sp_ms_marksystemobject
sp_msaddguidcolumn
sp_msaddguidindex
sp_msaddlogin_implicit_ntlogin
sp_msadduser_implicit_ntlogin
sp_mscheck_uid_owns_anything
sp_msdbuseraccess
sp_msdbuserpriv
sp_msdependencies
sp_msdrop_object
sp_msforeachdb
sp_msforeachtable
sp_msget_qualified_name
sp_msgettools_path
sp_msgetversion
sp_msguidtostr
sp_mshelpcolumns
sp_mshelpindex
sp_mshelptype
sp_msindexspace
sp_msis_pk_col
sp_mskilldb
sp_msloginmappings
sp_mstablekeys
sp_mstablerefs
sp_mstablespace
sp_msunc_to_drive
sp_msuniquecolname
sp_msuniquename
sp_msuniqueobjectname
sp_msuniquetempname
sp_tempdbspace
sp_who2
xp_delete_file
xp_dirtree
xp_enum_oledb_providers
xp_enumcodepages
xp_enumdsn
xp_enumerrorlogs
xp_enumgroups
xp_fileexist
xp_fixeddrives
xp_get_mapi_default_profile
xp_get_mapi_profiles
xp_getnetname
xp_qv
xp_readerrorlog
xp_regaddmultistring
xp_regdeletekey
xp_regdeletevalue
xp_regenumvalues
xp_regread
xp_regremovemultistring
xp_regwrite
xp_subdirs
xp_varbintohexstr

Bom por hoje é isso.

Um abraço,

Tiago Neves