Procedimientos no documentados sp_MSforeachtable y sp_MSforeachdb. Parte I

Microsoft SQL Server ofrece dos procedimientos almacenados sin documentar que le permiten procesar todas las tablas de una base de datos o bases de datos todo en una instancia de SQL Server. El primer procedimiento almacenado (SP), "sp_MSforeachtable", le permite procesar fácilmente un código contra todas las tablas en una sola base de datos. El SP otro ", sp_MSforeachdb", se ejecutará una instrucción T-SQL contra cualquier base de datos asociada a la instancia actual de SQL Server.

El "sp_MSforeachtable" SP viene con SQL Server, pero no está documentado en Books online. Este SP se pueden encontrar en la BD Master y se utiliza para procesar un solo comando T-SQL o un número de diferentes comandos T-SQL en contra de todas las tablas de una base de datos dada. Para demostrar cómo funciona vamos con un ejemplo.

Digamos que quiero crear una tabla temporal que contendrá una serie de registros, uno para cada tabla en la base de datos y que en cada fila contenga el nombre de la tabla y el número de filas de la tabla dada. Para hacer esto se desea ejecutar un comando como "select '' count (*) from '' where ''" donde por cada tabla en su base de datos se debe insertar los resultados en una tabla temporal. Entonces, vamos a ver cómo podemos hacer esto usando un cursor y luego usar el SP "sp_MSforeachtable":

Con cursor:

use pubs
go
set nocount on
declare @cnt int
declare @table varchar(128)
declare @cmd varchar(500)
create table #rowcount (tablename varchar(128), rowcnt int)
declare tables cursor for
select table_name from information_schema.tables
   where table_type = 'base table'
open tables
fetch next from tables into @table
while @@fetch_status = 0
begin
  set @cmd = 'select ''' + @table + ''', count(*) from ' + @table
  insert into #rowcount exec (@cmd)
  fetch next from tables into @table
end
CLOSE tables
DEALLOCATE tables
select top 5 * from #rowcount
    order by tablename
drop table #rowcount

Con "sp_MSforeachtable":

use pubs
go
create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable
   'insert into #rowcount select ''?'', count(*) from ?'
select top 5 * from #rowcount
    order by tablename
drop table #rowcount

Ambos tienen salidas similares, pero el segundo es más corto y de mejor rendimiento. Esta es la descripción de sus argumentos:

exec @RETURN_VALUE=sp_MSforeachtable @command1, @replacechar, @command2,
  @command3, @whereand, @precommand, @postcommand

donde

• @RETURN_VALUE - es el valor de retorno 
• @command1 - es el primer comando en ser ejecutado por "sp_MSforeachtable" y definido como un nvarchar(2000)
• @replacechar - es un caracter que podrá ser reemplazado con el nombre de la tabla que esta siendo procesada (valor por omisión es "?")
• @command2 y @command3 son comando adicionales que puede sercorridos para cada tabla, donde @command2 correo después de @command1, y @command3 correrá luego de @command2
• @whereand - este argumento puede ser usadopara agregar  constraints adicionales para ayudar a identificar las filas en la tabla sysobjects que será seleccionada, este argumento es un nvarchar(2000)
• @precommand - es un argumento nvarchar(2000) que especifica un comado para ser ejecutado antes que se procese cualquier tabla.
• @postcommand - también es un nvarchar(2000) que sirve para identificar un comando que será ejecutado luego de todos los comandos que han sido ejecutados.

Veamos un ejemplo con @whereand donde el nombre de una tabla empiece con "p":

use pubs
go
create table #rowcount (tablename varchar(128), rowcnt int)
exec sp_MSforeachtable
@command1 = 'insert into #rowcount select ''?'',
              count(*) from ?',
@whereand = 'and name like ''p%'''
select top 5 * from #rowcount
    order by tablename
drop table #rowcount

Bueno luego seguimoes este tema, acá los dejo por hoy.

Procedimiento almacenado sp_executesql

Para ejecutar una cadena, se recomienda utilizar el procedimiento almacenado sp_executesql, en lugar de una instrucción EXECUTE: ver aquí