Comment utiliser SP_MSFOREACHDB dans SQL Server

Lorsque vous travaillez avec des bases de données, vous rencontrerez des cas où vous devez exécuter un ensemble spécifique de requêtes sur toutes les bases de données. Il y a une procédure pratique appelée SP_MSFOREACHDB () dans un tel scénario. Cette procédure vous permet d'exécuter un ensemble de commandes sur chaque base de données disponible dans l'instance SQL Server.

En utilisant ce guide, vous apprendrez à utiliser la procédure stockée SP_MSForEachDB (), comment l'utiliser et divers exemples d'utilisation de la procédure.

Système.sp_msForEachdb ()

Le SP_MSForEachDB () est une procédure stockée sans papiers disponible dans la base de données maîtresse. Il vous permet de faire une parole sur toutes les bases de données de l'instance SQL Server et d'exécuter des requêtes SQL par rapport aux bases de données spécifiées.

Dans SQL Server Management Studio, vous pouvez afficher cette procédure en accédant à la base de données maître -> Programmabilité -> Procédures stockées -> Procédures stockées système.

Nous pouvons exprimer la syntaxe de procédure comme indiqué:

Déclarer @Command Varchar (255)
Définir @command = 'Operations de commande'
Exec SP_MSFOREACHDB @ Commande = Commande

Regardons maintenant quelques exemples d'utilisation de la procédure.

Exemple 1-présentant les noms de toutes les bases de données

Supposons que vous souhaitiez obtenir les noms de toutes les bases de données de l'instance SQL Server; Vous pouvez utiliser la procédure MSForEachDB () comme indiqué dans l'exemple ci-dessous:

Déclarer @Command Varchar (255)
Set @ command = 'use ? imprimer db_name () '
Exec SP_MSFOREACHDB @COMMAND

L'ensemble de requêtes ci-dessus doit renvoyer les noms de toutes les bases de données de l'instance. Un exemple de sortie est comme indiqué:

maître
tempdb
modèle
msdb
Salesdb
Baseballdata
WideworldImporters
Temps d'achèvement: 2021-12-14T02: 43: 45.8852391-08: 00

Exemple 2 - Afficher les tailles de base de données

Bien qu'il existe différentes façons d'utiliser pour obtenir la taille d'une base de données dans SQL Server, dans cet exemple, nous utiliserons la procédure sp_spaceUsed.

Considérez l'exemple ci-dessous:

Déclarer @Command Varchar (255)
Set @ command = 'use [?]] exec sp_spaceUsed '
Exec SP_MSFOREACHDB @COMMAND

À l'aide d'une seule commande, nous pouvons afficher la taille de toutes les bases de données comme indiqué dans l'exemple de sortie ci-dessous:

Exemple 3 - Afficher toutes les colonnes dans les bases de données

Pour afficher les colonnes dans chaque base de données, vous pouvez exécuter une requête comme indiqué dans l'exemple d'extrait ci-dessous:

Declare @Command Varchar (255);
Set @ command = 'sélectionner le nom dans ?.système.Colonnes'
Exec SP_MSFOREACHDB @COMMAND

La requête ci-dessus doit renvoyer les colonnes dans chaque base de données comme indiqué:

Exemple 4 - Réduire toutes les bases de données

Vous pouvez réduire la taille de toutes les bases de données du serveur à l'aide de la procédure MSForEachDB comme indiqué ci-dessous:

Declare @Command Varchar (255);
Set @ command = 'dbcc shrinkDatabase (' '?'', 0) '
Exec SP_MSFOREACHDB @COMMAND

La requête d'exemple ci-dessus essaiera de réduire la taille de toutes les bases de données du serveur. Si vous avez une collection complète de bases de données, évitez d'utiliser cette requête car elle peut prendre beaucoup de temps et empêcher d'autres processus d'utiliser les bases de données.

L'exemple de sortie est comme indiqué:

Fermeture

Fermeture
Ce didacticiel vous montre comment utiliser la procédure stockée SP_MSFOREACHDB () pour exécuter des requêtes SQL sur toutes les bases de données de l'instance SQL Server.

Merci d'avoir lu et restez à l'écoute pour plus de tutoriels SQL Server.