Maîtriser l’interface de ligne de commande SQL Server

SQL Server possède l’un des meilleurs outils de gestion nommé SQL Server Management Studio (alias SSMS). Il offre de nombreuses fonctionnalités qui facilitent grandement la vie des développeurs et des administrateurs de base de données. Mais parfois, certains problèmes ne peuvent pas être résolus à l’aide de SQL Server Management Studio. Surtout lorsqu’il s’agit d’exécuter des scripts SQL ad hoc ou de récupérer des instances SQL Server en panne. Dans de tels cas d’utilisation, vous pouvez utiliser SQLCMD.

Il s’agit d’un article sponsorisé par Devart. Devart est actuellement l’un des principaux fournisseurs de logiciels de gestion de bases de données et de solutions ALM pour les serveurs de bases de données les plus populaires.

Premiers pas avec SQLCMD

Dans cet article, nous découvrirons SQLCMD. Il s’agit d’un outil en ligne de commande qui peut être utilisé pour les tâches suivantes :

• Exécutez des requêtes SQL ad hoc et des procédures stockées sur des serveurs locaux et distants
• Exporter la sortie de la requête SQL vers des fichiers texte ou CSV
• Gérer et administrer les instances et bases de données SQL Server sous Windows et Linux

Pour rendre l’écriture de requêtes dans SSMS plus rapide et plus facile, ainsi que pour l’améliorer avec des fonctionnalités supplémentaires pour la gestion et l’administration de bases de données, nous l’avons amélioré avec Outils SQL dbForgeun pack de compléments parfaitement intégrés à SSMS.

Commençons maintenant par l’installation.

Pour installer l’utilitaire SQLCMD, vous devez sélectionner les outils clients natifs de SQL Server lors de l’installation de SQL Server. Vous pouvez également l’installer séparément à l’aide du gestionnaire d’installation de SQL Server.

L’utilitaire SQLCMD peut être invoqué en tapant simplement SQLCMD dans PowerShell ou dans l’invite de commande. Vous pouvez voir la liste des options pouvant être utilisées avec SQLCMD en exécutant la commande suivante :

PS C:\Users\nisar> SQLCMD -?

Voici à quoi ressemble le résultat de la ligne de commande.

Connexion à SQL Server à l’aide de SQLCMD

Voyons maintenant comment se connecter à une instance SQL Server à l’aide de SQLCMD.

Exemple 1 : Connectez-vous à l’instance SQL Server par défaut

Pour vous connecter à SQL Server sur une machine locale, utilisez la commande SQLCMD suivante :

C:\Users\nisar>sqlcmd -S Nisarg-PC

Comme vous pouvez le voir, le résultat de la commande est 1> qui montre que vous êtes connecté à SQL Server.

Notez que si vous vous connectez à l’instance par défaut de SQL Server sur une machine locale, vous n’avez pas besoin de spécifier explicitement le nom d’hôte/nom de serveur.

Exemple 2 : Connectez-vous à une instance SQL Server nommée

Voyons maintenant un autre exemple montrant comment se connecter à une instance SQL Server nommée.

Pour vous connecter à une instance SQL Server nommée, vous devez spécifier le paramètre -S (nom du serveur). Par exemple, si le nom de votre serveur est MonServeur et l’instance nommée est SQL2017la commande pour s’y connecter à l’aide de SQLCMD serait :

C:\>sqlcmd -S Nisarg-PC\SQL2019

Voici le résultat.

Exemple 3 : Connectez-vous à SQL Server à l’aide de l’authentification Windows et de l’authentification SQL Server

Voyons maintenant comment se connecter à SQL Server à l’aide de l’authentification Windows et SQL Server.

Pour vous connecter à SQL Server à l’aide de SQLCMD, vous pouvez utiliser l’authentification Windows et l’authentification SQL Server. Si vous souhaitez utiliser l’authentification SQL Server, vous devez spécifier les options -U (nom d’utilisateur) et -p (mot de passe). Si vous ne spécifiez pas de mot de passe, l’utilitaire SQLCMD vous demandera de saisir le mot de passe. La capture d’écran suivante illustre cela.

Travailler avec SQLCMD en mode interactif

Dans cette section, nous verrons comment exécuter SQLCMD en mode interactif, exécuter des requêtes SQL et afficher la sortie. Le mode interactif permet d’écrire des instructions et des commandes SQL. Commençons par apprendre à se connecter à SQL Server, à passer en mode interactif et à exécuter des requêtes dans SQLCMD.

Exemple 1 : Remplir une liste de bases de données avec les propriétaires

Tout d’abord, connectez-vous à votre serveur de base de données à l’aide de la commande suivante :

C:\>sqlcmd -S Nisarg-PC -U sa -p

Une fois la session interactive commencée, exécutez la requête SQL suivante dans l’utilitaire SQLCMD :

use master;
select a.name,b.name from sys.databases a inner join sys.server_principals b
on a.owner_sid=b.sid where a.name not in ('ReportServer','ReportServerTempDB')
and a.database_id>5;

Voici le résultat de la requête.

Comme vous pouvez le constater, la requête ci-dessus a rempli la liste des bases de données avec les propriétaires de bases de données.

Exemple 2 : Vérifier la base de données actuelle

Tout d’abord, connectez-vous au serveur de base de données et exécutez la requête suivante :

Select DB_NAME()
Go

Il s’agit du résultat de la requête.

La requête a renvoyé la base de données principale car je n’ai pas défini la base de données par défaut pour la connexion que j’utilise pour me connecter à SQL Server.

Exemple 3 : exécuter des requêtes SQL

Vous pouvez exécuter des requêtes SQL à l’aide de SQLCMD en spécifiant le -Q paramètre. Par exemple, vous souhaitez afficher la liste des tables créées dans le Gestion Scolaire base de données utilisant SQLCMD. La commande doit être écrite comme suit :

C:\>sqlcmd -S Nisarg-PC -d SchoolManagement -Q "select name from sys.tables"

Jetez un œil au résultat de la requête.

De même, vous pouvez également exécuter d’autres requêtes. Veuillez noter que la connexion que vous utilisez pour vous connecter à SQL Server doit disposer de l’autorisation requise sur la base de données.

Travailler avec SQLCMD dans l’invite de commande

C’est ici que nous verrons comment exécuter des scripts SQL via l’invite de commande. Cette fonctionnalité est utile lorsque vous souhaitez exécuter des tâches d’automatisation, des opérations groupées et des requêtes de longue durée qui ne nécessitent aucune intervention de l’utilisateur.

J’ai créé un script SQL contenant une requête SQL utilisée pour remplir la liste des objets créés dans le Importateurs du monde entier base de données. La requête est la suivante :

use [WideWorldImporters]
go
select name, type_desc, create_date from sys.objects where type_desc <>'SYSTEM_TABLE'

Ajoutez la requête ci-dessus à un script SQL nommé sp_get_db_objects.sql. Exportons maintenant la sortie vers un fichier texte nommé base de données_objets.txt.

Pour cela, nous utiliserons les options suivantes :

• -o : Spécifiez le fichier de destination. Dans cette démo, le fichier texte de destination s’appelle WideWorldImportores_objects.txt.
• -i : Spécifiez l’emplacement du script SQL. Dans cette démo, le script SQL s’appelle DBObjects.sql.

Maintenant, exécutons la commande suivante :

sqlcmd -S Nisarg-PC -i D:\Scripts\DBObjects.sql -o D:\Scripts\WideWorldImportores_objects.txt

Une fois la commande terminée avec succès, il est temps de consulter le fichier texte.

Comme vous pouvez le voir dans la capture d’écran ci-dessus, la requête a été exécutée avec succès.

Maintenant, prenons un autre exemple. Ici, nous allons apprendre comment générer une sauvegarde de StackOverflow2010 à l’aide d’un script SQL. La requête pour générer une sauvegarde est la suivante :

use master
go
backup database [Stackoverflow2010] to disk ='D:\SQLBackups\Stackoverflow2010.bak' with compression, stats=5

J’ai stocké la commande de sauvegarde dans un script SQL nommé StackOverflow2010_backup_script.sql. Pour exécuter le script, la commande SQLCMD sera la suivante :

Capture d’écran 1 :

Comme vous pouvez le voir dans la capture d’écran ci-dessus, la sauvegarde a été générée.

Capture d’écran 2 :

Utilisation de SQLCMD dans SQL Server Management Studio

Pour utiliser SQLCMD dans SSMS, vous devez d’abord activer le mode SQLCMD. Pour ce faire, sélectionnez Requête dans le menu et sélectionnez Mode SQLCMDcomme le montre l’image suivante :

Si vous souhaitez définir le mode SQLCMD par défaut, accédez à Outils → Possibilités. Dans Possibilitéssélectionner Exécution de requête → serveur SQL → Général et sélectionnez le Par défaut, ouvrez les nouvelles requêtes en mode SQLCMD case à cocher.

Voyons maintenant comment l’utiliser.

Par exemple, je souhaite obtenir le total des enregistrements du Des postes tableau du Stackoverflow2010 base de données. Pour ce faire, la requête doit être écrite comme suit :

:SETVAR TABLENAME "Posts"
:SETVAR DATABASENAME "Stackoverflow2010"
use $(DATABASENAME);
select count(1) from $(TABLENAME);
GO

Maintenant, exécutons la requête. La capture d’écran suivante montre le résultat de la requête.

Voyons maintenant comment utiliser SQLCMD dans PowerShell.

Utilisation de SQLCMD dans PowerShell

Vous pouvez appeler SQLCMD à l’aide de PowerShell. Pour ce faire, vous devez installer PowerShell pour SQL Server. Tu peux lire Cet article pour en savoir plus sur PowerShell pour SQL Server et comment l’installer.

Prenons un exemple simple. Supposons que je souhaite obtenir la liste des procédures stockées du Importateurs du monde entier base de données. La commande PowerShell est la suivante :

PS C:\WINDOWS\system32> invoke-sqlcmd -database wideworldimporters -query "select name from sys.procedures"

Voici le résultat.

Un autre exemple montre comment exporter la sortie d’un script SQL vers un fichier texte en utilisant SQLPS. Supposons que nous souhaitions exporter une liste de tâches d’agent SQL Server. J’ai créé un script nommé SQLJobs.sql qui récupère la liste des tâches SQL. Le script contient la commande T-SQL suivante :

use [msdb]
go
select name, description,date_created from Sysjobs

Pour exécuter le script, j’exécute la commande suivante dans PowerShell pour SQL Server.

invoke-sqlcmd -inputfile "D:\Scripts\SQLJobs.sql" | Out-File -FilePath "D:\Scripts\SQLJobs_List.txt"

Une fois la commande terminée, j’ouvre le fichier de sortie qui se présente comme suit.

Techniques SQLCMD avancées

Voici quelques techniques avancées qui peuvent vous aider à utiliser SQLCMD plus efficacement. Je vais les expliquer avec des exemples simples.

Exemple 1 : Afficher les messages d’erreur en fonction du niveau de gravité de l’erreur

Cet exemple montre comment afficher un message d’erreur en fonction de son niveau de gravité. Cette méthode peut être utilisée en ajoutant le -m option. Supposons que vous exécutiez une requête SELECT sur un objet de base de données inexistant. La commande reviendra « Objet invalide »et le niveau de gravité de cette erreur est 16. Voir la capture d’écran suivante.

Jetons un coup d’œil à une erreur qui a un niveau de gravité de 15 – une erreur de syntaxe

Comme vous pouvez le voir dans la capture d’écran ci-dessus, la gravité de l’erreur est de 15, donc SQLCMD n’a affiché aucune erreur.

Exemple 2 : quitter la session SQLCMD lorsqu’une erreur se produit

Cet exemple montre comment quitter votre session SQLCMD lorsqu’une commande ou une requête rencontre une erreur. Pour ce faire, vous devez préciser le -b option. Supposons que vous souhaitiez quitter SQLCMD lorsque la requête rencontre un « la base de données n’existe pas » erreur.

Exemple 3 : Accepter la saisie de l’utilisateur

Cet exemple montre comment accepter les entrées de l’utilisateur lors de l’exécution d’un script T-SQL. Cela implique des variables de script dans SQLCMD. Pour démontrer cela, j’ai créé un script qui remplit le nom officiel du pays. Le script utilise le Importateurs du monde entier base de données et le application.Pays tableau. Le contenu du script est le suivant :

use [WideWorldImporters]
Go
select CountryName, FormalName from application.countries where CountryName=$(CountryName)
Go

Maintenant, j’enregistre le script et je l’exécute à l’aide de la commande SQLCMD suivante :

sqlcmd -S Nisarg-PC -v CountryName="India" -i D:\Scripts\Asia_Countries.sql

Voici le résultat.

Comme vous pouvez le voir, la requête a renvoyé le nom formel Inde.

Conclusion

Dans cet article, vous avez découvert la commande SQLCMD et comment l’utiliser avec divers exemples. SQLCMD est un outil puissant qui peut vous aider à exécuter des scripts, à exporter votre sortie vers divers fichiers et à administrer SQL Server. Vous pouvez également utiliser la DAC (Dedicated Admin Connection), qui permet d’accéder aux serveurs de bases de données endommagés ou corrompus.

Enfin, vous pouvez toujours améliorer les capacités de stock de SSMS avec la complétion et le formatage intelligents du code, le contrôle de source, les tests unitaires, l’automatisation de la ligne de commande et de nombreux autres éléments utiles disponibles dans des offres groupées telles que Outils SQL dbForge.

Les nouveaux utilisateurs peuvent leur proposer un essai gratuit pendant 30 jours. Une fois le bundle installé, tous les compléments seront facilement disponibles à partir du menu SSMS et de l’Explorateur d’objets. Ils me font gagner tellement de temps que je ne peux m’empêcher de les recommander.