Cómo usar SP_MSFOREACHDB en SQL Server

Al trabajar con bases de datos, encontrará instancias en las que necesita ejecutar un conjunto específico de consultas en todas las bases de datos. Hay un procedimiento práctico llamado SP_MSFOREACHDB () en dicho escenario. Este procedimiento le permite ejecutar un conjunto de comandos en cada base de datos disponible en la instancia de SQL Server.

Usando esta guía, aprenderá cómo usar el procedimiento almacenado SP_MSFOREACHDB (), cómo usarla y varios ejemplos de cómo usar el procedimiento.

Sys.SP_MSFOREACHDB ()

El SP_MSFOREACHDB () es un procedimiento almacenado indocumentado disponible en la base de datos maestro. Le permite recorrer todas las bases de datos en la instancia de SQL Server y ejecutar consultas SQL en las bases de datos especificadas.

En SQL Server Management Studio, puede ver este procedimiento navegando a la base de datos maestra -> Programabilidad -> Procedimientos almacenados -> Procedimientos almacenados del sistema.

Podemos expresar la sintaxis del procedimiento como se muestra:

Declarar @Command Varchar (255)
Set @Command = 'Operaciones de comando'
Exec sp_msforeachdb @command = comando

Veamos ahora algunos ejemplos de uso del procedimiento.

Ejemplo 1-show los nombres de todas las bases de datos

Supongamos que desea obtener los nombres de todas las bases de datos en la instancia de SQL Server; Puede usar el procedimiento MSFOREACHDB () como se muestra en el ejemplo a continuación:

Declarar @Command Varchar (255)
Set @command = 'use ? Imprima db_name () '
Ejec SP_MSFOREACHDB @Command

El conjunto anterior de consultas debe devolver los nombres de todas las bases de datos en la instancia. Una salida de ejemplo es como se muestra:

maestro
tempdb
modelo
MSDB
saleDb
Langua de béisbol
Amplioworldimporters
Tiempo de finalización: 2021-12-14T02: 43: 45.8852391-08: 00

Ejemplo 2 - Mostrar tamaños de base de datos

Aunque hay varias formas de usar para obtener el tamaño de una base de datos en SQL Server, en este ejemplo, utilizaremos el procedimiento SP_Spaceused.

Considere el ejemplo que se muestra a continuación:

Declarar @Command Varchar (255)
Set @command = 'use [?]; ejecutor sp_spaceused '
Ejec SP_MSFOREACHDB @Command

Usando un solo comando, podemos ver el tamaño de todas las bases de datos como se muestra en la salida de ejemplo a continuación:

Ejemplo 3 - Muestre todas las columnas en las bases de datos

Para ver las columnas en cada base de datos, puede ejecutar una consulta como se muestra en el fragmento de ejemplo a continuación:

Declarar @Command Varchar (255);
Set @command = 'Seleccione Nombre de ?.sys.columnas '
Ejec SP_MSFOREACHDB @Command

La consulta anterior debe devolver las columnas en cada base de datos como se muestra:

Ejemplo 4: encoge todas las bases de datos

Puede encoger el tamaño de todas las bases de datos en el servidor utilizando el procedimiento MSFORECHDB como se muestra a continuación:

Declarar @Command Varchar (255);
Set @command = 'DBCC ShrinkDatabase (' '?'', 0) '
Ejec SP_MSFOREACHDB @Command

La consulta de ejemplo anterior intentará reducir el tamaño de todas las bases de datos en el servidor. Si tiene una colección integral de bases de datos, evite usar esta consulta, ya que puede llevar mucho tiempo y bloquear otros procesos para usar las bases de datos.

El ejemplo de salida es como se muestra:

Clausura

Clausura
Este tutorial le muestra cómo usar el procedimiento almacenado SP_MSFOREACHDB () para ejecutar consultas SQL en todas las bases de datos en la instancia de SQL Server.

Gracias por leer y estad atentos para más tutoriales de SQL Server.