Mastering the SQL Server command-line interface

SQL Server has among the best administration instruments named SQL Server Administration Studio (a.ok.a. SSMS). It affords a number of options that make the lifetime of builders and DBAs a lot simpler. However generally, there are some points that can not be mounted utilizing SQL Server Administration Studio. Particularly on the subject of operating ad-hoc SQL scripts or recovering crashed SQL Server cases. In such use circumstances, you need to use SQLCMD.

It is a sponsored article by Devart. Devart is at present one of many main suppliers of database administration software program and ALM options for the most well-liked database servers.

Getting began with SQLCMD

On this article, we’ll study SQLCMD. It’s a command-line instrument that can be utilized for the next duties:

• Run ad-hoc SQL queries and saved procedures on native and distant servers
• Export SQL question output to textual content or CSV recordsdata
• Handle and administer SQL Server cases and databases on Home windows and Linux

To make question writing in SSMS quicker and simpler, in addition to to reinforce it with extra options for database administration and administration, we enhanced it with dbForge SQL Instruments, a pack of add-ins seamlessly built-in into SSMS.

Now allow us to start with set up. 

To put in the SQLCMD utility, it’s essential to choose the native SQL Server shopper instruments whereas putting in SQL Server. You can even set up it individually utilizing the SQL Server set up supervisor.

The SQLCMD utility might be invoked by simply typing SQLCMD in PowerShell or within the command immediate. You may see the checklist of choices that can be utilized with SQLCMD by operating the next command:

PS C:Usersnisar> SQLCMD -?

That is what the command-line output appears to be like like.

Connecting to SQL Server utilizing SQLCMD

Now, allow us to perceive how to hook up with a SQL Server occasion utilizing SQLCMD.

Instance 1: Hook up with the default SQL Server occasion

To connect with SQL Server on an area machine, use the next SQLCMD command:

C:Usersnisar>sqlcmd -S Nisarg-PC

As you’ll be able to see, the command output is 1> which exhibits that you’re linked to SQL Server.

Word that if you’re connecting to the default occasion of SQL Server on an area machine, you don’t have to explicitly specify the hostname/server identify.

Instance 2: Hook up with a named SQL Server occasion

Now, allow us to examine one other instance displaying how to hook up with a named SQL Server occasion.

To connect with a named SQL Server occasion, it’s essential to specify the parameter -S (server identify) As an illustration, in case your server’s identify is MyServer and the named occasion is SQL2017, the command to hook up with it utilizing SQLCMD can be:

C:>sqlcmd -S Nisarg-PCSQL2019

Right here is the output.

Instance 3: Hook up with SQL Server utilizing Home windows authentication and SQL Server authentication

Now, allow us to see how to hook up with SQL Server utilizing Home windows and SQL Server authentication.

To connect with SQL Server utilizing SQLCMD, you need to use Home windows authentication and SQL Server authentication. If you wish to use SQL Server authentication, it’s essential to specify the -U (person identify) and -p (password) choices. If you don’t specify the password, the SQLCMD utility will ask you to enter the password. The next screenshot illustrates this.

Working with SQLCMD within the interactive mode

On this part, we’ll see learn how to run SQLCMD within the interactive mode, execute SQL queries, and consider the output. The interactive mode permits writing SQL statements and instructions. Let’s begin with studying how to hook up with SQL Server, enter the interactive mode, and run queries in SQLCMD.

Instance 1: Populate an inventory of databases with homeowners

First, hook up with your database server utilizing the next command:

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

As soon as the interactive session begins, run the next SQL question within the SQLCMD utility:

use grasp;
choose a.identify,b.identify from sys.databases a inside be a part of sys.server_principals b
on a.owner_sid=b.sid the place a.identify not in ('ReportServer','ReportServerTempDB')
and a.database_id>5;

Right here is the question output.

As you’ll be able to see, the above question has populated the checklist of databases with database homeowners.

Instance 2: Verify the present database

First, hook up with the database server and execute the next question:

Choose DB_NAME()
Go

That is the question output.

The question has returned the grasp database as a result of I’ve not set the default database for the login which I’m utilizing to hook up with SQL Server.

Instance 3: Execute SQL queries

You may run SQL queries utilizing SQLCMD by specifying the -Q parameter. For instance, you need to view the checklist of tables created within the SchoolManagement database utilizing SQLCMD. The command ought to be written as follows:

C:>sqlcmd -S Nisarg-PC -d SchoolManagement -Q "choose identify from sys.tables"

Check out the question output.

Equally, you’ll be able to run different queries as effectively. Please observe that the login you’re utilizing to hook up with SQL Server will need to have the required permission on the database.

Working with SQLCMD within the command immediate

That is the place we’ll see learn how to execute SQL scripts by way of the command immediate. This characteristic is helpful whenever you need to run automation duties, bulk operations, and long-running queries that don’t require any person enter.

I’ve created an SQL script that accommodates an SQL question that’s used to populate the checklist of objects created within the WideWorldImporters database. The question is as follows:

use [WideWorldImporters]
go
choose identify, type_desc, create_date from sys.objects the place type_desc <>'SYSTEM_TABLE'

Add the above question to a SQL script named sp_get_db_objects.sql. Now let’s export the output to a textual content file named database_objects.txt.

For that, we’ll use the next choices:

• -o: Specify the vacation spot file. On this demo, the vacation spot textual content file known as WideWorldImportores_objects.txt.
• -i: Specify the situation of the SQL script. On this demo, the SQL script known as DBObjects.sql.

Now, let’s run the next command:

sqlcmd -S Nisarg-PC -i D:ScriptsDBObjects.sql -o D:ScriptsWideWorldImportores_objects.txt

As soon as the command is efficiently accomplished, it’s time to evaluate the textual content file.

As you’ll be able to see within the above screenshot, the question has been executed efficiently.

Now, allow us to take one other instance. Right here we’ll learn to generate a backup of StackOverflow2010 utilizing a SQL script. The question to generate a backup is as follows:

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

I’ve saved the backup command in an SQL script named StackOverflow2010_backup_script.sql. To execute the script, the SQLCMD command will likely be as follows:

Screenshot 1:

As you’ll be able to see within the above screenshot, the backup has been generated.

Screenshot 2:

Utilizing SQLCMD in SQL Server Administration Studio

To make use of SQLCMD in SSMS, first, you should allow the SQLCMD mode. To do this, choose Question from the menu and choose SQLCMD Mode, as proven within the following picture:

If you wish to set the SQLCMD mode by default, go to Instruments → Choices. In Choices, choose Question execution → SQL Server → Basic and choose the By default, open new queries in SQLCMD mode checkbox.

Now, allow us to see learn how to use it.

For instance, I need to get the full information of the Posts desk of the Stackoverflow2010 database. To do this, the question ought to be written as follows:

:SETVAR TABLENAME "Posts"
:SETVAR DATABASENAME "Stackoverflow2010"
use $(DATABASENAME);
choose rely(1) from $(TABLENAME);
GO

Now, let’s run the question. The next screenshot exhibits the question output.

Now, allow us to see learn how to use SQLCMD in PowerShell.

Utilizing SQLCMD in PowerShell

You may invoke SQLCMD utilizing PowerShell. To do this, you should set up PowerShell for SQL Server. You may learn this text to study extra about PowerShell for SQL Server and learn how to set up it.

Allow us to take a easy instance. Suppose I need to get the checklist of saved procedures of the WideWorldImporters database. The PowerShell command is as follows:

PS C:WINDOWSsystem32> invoke-sqlcmd -database wideworldimporters -query "choose identify from sys.procedures"

Right here is the output.

One other instance exhibits learn how to export the output of an SQL script to a textual content file utilizing sqlps. Suppose we need to export an inventory of SQL Server agent jobs. I’ve created a script named SQLJobs.sql which retrieves the checklist of SQL jobs. The script accommodates the next T-SQL command:

use [msdb]
go
choose identify, description,date_created from Sysjobs

To run the script, I execute the next command in PowerShell for SQL Server.

invoke-sqlcmd -inputfile "D:ScriptsSQLJobs.sql" | Out-File -FilePath "D:ScriptsSQLJobs_List.txt"

As soon as the command is accomplished, I open the output file, which appears to be like as follows.

Superior SQLCMD strategies

Listed below are a number of superior strategies that may assist you use SQLCMD extra successfully. I’m going to clarify them with easy examples.

Instance 1: Present error messages based on the error severity stage

This instance exhibits learn how to show an error message based on its severity stage. This technique can be utilized by including the -m choice. Suppose you’re operating a SELECT question towards a non-existing database object. The command will return “Invalid object”, and the severity stage of that error is 16. See the next screenshot.

Let’s check out an error that has a severity stage of 15 – a syntax error

As you’ll be able to see within the above screenshot, the error severity is 15, due to this fact SQLCMD didn’t present any error.

Instance 2: Exit the SQLCMD session when an error happens

This instance exhibits learn how to exit your SQLCMD session when a command or question encounters an error. To do this, you should specify the -b choice. Suppose you need to exit SQLCMD when the question encounters a “database doesn’t exist” error.

Instance 3: Settle for person enter

This instance exhibits learn how to settle for person enter whereas executing a T-SQL script. This entails scripting variables in SQLCMD. To reveal that, I’ve created a script that populates the formal identify of the nation. The script makes use of the WideWorldImporters database and the software.Nations desk. The content material of the script is as follows:

use [WideWorldImporters]
Go
choose CountryName, FormalName from software.international locations the place CountryName=$(CountryName)
Go

Now I save the script and execute it utilizing the next SQLCMD command:

sqlcmd -S Nisarg-PC -v CountryName="India" -i D:ScriptsAsia_Countries.sql

Right here is the output.

As you’ll be able to see, the question returned the formal identify India.

Conclusion

On this article, you’ve got discovered concerning the SQLCMD command and learn how to use it with numerous examples. SQLCMD is a strong instrument that may assist you run scripts, export your output to varied recordsdata, and administer SQL Server. You can even use the DAC (Devoted Admin Connection), which helps entry broken or corrupted database servers.

Lastly, you’ll be able to at all times energy up the inventory capabilities of SSMS with clever code completion and formatting, supply management, unit testing, command-line automation, and loads of different helpful stuff obtainable in bundles like dbForge SQL Instruments. 

New customers may give them a free take a look at drive for a whopping 30 days. As soon as you put in the bundle, all of the add-ins will likely be conveniently obtainable from each the SSMS menu and Object Explorer. They save me a lot time that I can’t assist however advocate them.