Vamos a presentar dos procedimientos almacenados muy útiles que se alojan en la base de datos master pero que no son mencionados en los libros en pantalla. Estos SPs son ideales para obtener el espacio utilizado, el número de filas, los índices en las tablas de usuario y demás.
El primer procedimiento, sp_MSForEachDB ejecuta tres comandos contra cada base de datos en el servidor.
|> @command1: primer comando a ejecutar
|> @replacechar: sustituye otro marcador de posición por la interrogación ?
|> @command2: segundo comando a ejecutar
|> @command3: tercer comando a ejecutar
|> @precommand: ejecuta el comando una vez antes de entrar en el bucle
|> @postcommand: ejecuta el comando una vez después de salir del bucle
Cada conjunto de comandos (incluso si sólo consiste en un único comando) se ejecuta como un batch contra las bases de datos, de forma que es más útil capturar el texto de salida que el típico grid de conjunto de resultados. Elegimos entonces que los resultados de la consulta se muestren como texto (pulsando Ctrl + T)
El siguiente código devuelve el espacio utilizado en cada base de datos del servidor:
exec sp_MSForEachDB
@command1 = “use ? exec sp_SpaceUsed”
La salida sería similar a esto:
database_name database_size unallocated space
——————————————————————————————————————————– —————— ——————
master 6.50 MB 1.35 MB
reserved data index_size unused
—————— —————— —————— ——————
2712 KB 1152 KB 1032 KB 528 KB
database_name database_size unallocated space
——————————————————————————————————————————– —————— ——————
tempdb 2.69 MB 1.03 MB
El segundo procedimiento, sp_MSForEachTable acepta siete parámetros:
|> @command1: primer comando a ejecutar
|> @replacechar: sustituye otro marcador de posición por la interrogación ?
|> @command2: segundo comando a ejecutar
|> @command3: tercer comando a ejecutar
|> @wherend: condición where (o cláusula order by)
|> @precommand: ejecuta el comando una vez antes de entrar en el bucle
|> @postcommand: ejecuta el comando una vez después de salir del bucle
Asignando valores a los parámetros que queremos pasar, no tenemos necesidad de pasar valores nulos. El parámetro @replacechar es útil cuando los comandos que deseamos ejecutar contienen una interrogación. El parámetro @whereand permite establecer la salida en base a un filtro.
Podemos incluir una cláusula ORDER BY. El siguiente ejemplo devuelve el número de filas en cada tabla de la base de datos AdventureWorks y los ordena por nombre de tabla:
exec sp_MSForEachTable
@command1 = “Print ‘?’”
, @command2 = “select count(*) from ?”
, @whereand = “ORDER BY 1”
Nos quedaría una salida similar a esta:
[HumanResources].[Department]
———–
16
[HumanResources].[Employee]
———–
290
[HumanResources].[EmployeeAddress]
———–
290
[HumanResources].[EmployeeDepartmentHistory]
———–
296
* Traducción del artículo “Iterating through tables and databases in SQL Server” de Arthur Fuller publicado en www.sqlservercentral.com “