Mastering the SQL Server command-line interface

SQL Server comes with a powerful management tool called SQL Server Management Studio (SSMS) that provides developers and DBAs with many features to simplify their work. However, there are times when certain issues cannot be resolved using SSMS, especially when dealing with ad-hoc SQL scripts or recovering crashed SQL Server instances. In such cases, SQLCMD can be a useful tool.

This article is sponsored by Devart, a leading provider of database management software and ALM solutions for popular database servers.

Getting started with SQLCMD

In this article, we will explore SQLCMD, a command-line tool that can be used for various tasks, including:

• Running ad-hoc SQL queries and stored procedures on local and remote servers
• Exporting SQL query output to text or CSV files
• Managing and administering SQL Server instances and databases on Windows and Linux

To streamline query writing in SSMS and enhance it with additional features for database management and administration, we have enhanced it with dbForge SQL Tools, a set of add-ins seamlessly integrated into SSMS.

Let’s start with the installation process.

To install the SQLCMD utility, you can select the native SQL Server client tools during SQL Server installation or install it separately using the SQL Server Installation Manager.

You can invoke the SQLCMD utility by simply typing SQLCMD in PowerShell or the command prompt. To view the list of options available with SQLCMD, run the following command:

PS C:\Users\nisar> SQLCMD -?

Below is a sample of the command-line output:

Connecting to SQL Server using SQLCMD

Now, let’s learn how to connect to a SQL Server instance using SQLCMD.

Example 1: Connect to the default SQL Server instance

To connect to the default SQL Server instance on a local machine, use the following SQLCMD command:

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

The command output will show that you are connected to SQL Server.

It is worth noting that when connecting to the default instance of SQL Server on a local machine, the hostname/server name does not need to be explicitly specified.

Example 2: Connect to a named SQL Server instance

Let’s explore how to connect to a named SQL Server instance.

To connect to a named SQL Server instance, you must specify the -S (server name) parameter. For example, if your server’s name is MyServer and the named instance is SQL2017, the SQLCMD command to connect to it would be:

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

Here is the output of the command:

Example 3: Connect to SQL Server using Windows authentication and SQL Server authentication

Let’s see how to connect to SQL Server using Windows and SQL Server authentication.

To connect to SQL Server using SQLCMD, you can use Windows authentication and SQL Server authentication. If you opt for SQL Server authentication, you need to specify the -U (user name) and -p (password) options. If the password is not specified, SQLCMD will prompt you to enter it. The following screenshot illustrates this:

Working with SQLCMD in the interactive mode

In this section, we will learn how to run SQLCMD in interactive mode, execute SQL queries, and view the output. The interactive mode allows you to write SQL statements and commands. Let’s start by connecting to SQL Server, entering the interactive mode, and running queries in SQLCMD.

Example 1: Populate a list of databases with owners

First, connect to your database server using the following command:

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

Once the interactive session starts, run the following SQL query in 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;

Here is the output of the query:

The query successfully lists the databases with their owners.

Example 2: Check the current database

First, connect to the database server and execute the following query:

Select DB_NAME() Go

Here is the output of the query:

The query returned the master database as the default database for the login used to connect to SQL Server was not specified.

Example 3: Execute SQL queries

You can run SQL queries using SQLCMD by specifying the -Q parameter. For example, to view the list of tables created in the SchoolManagement database using SQLCMD, use the following command:

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

Here is the output of the query:

You can run other queries as well, ensuring that the login used to connect to SQL Server has the necessary permissions on the database.

Working with SQLCMD in the command prompt

This section covers executing SQL scripts via the command prompt, useful for running automation tasks, bulk operations, and long-running queries without user input.

I have created an SQL script containing a query to list objects created in the WideWorldImporters database:

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

Add this query to an SQL script named sp_get_db_objects.sql. Now, export the output to a text file named database_objects.txt using the following options:

• -o: Specify the destination file (WideWorldImportores_objects.txt in this demo)
• -i: Specify the location of the SQL script (DBObjects.sql in this demo)

Run the following command:

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

Once the command completes successfully, review the text file.

As seen in the screenshot, the query was executed successfully.

Let’s consider another example where we generate a backup of StackOverflow2010 using an SQL script:

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

Save this backup command in an SQL script named StackOverflow2010_backup_script.sql.

To execute the script, the SQLCMD command will be as follows:

Screenshot 1:

As you can see in the above screenshot, the backup has been generated.

Screenshot 2:

Using SQLCMD in SQL Server Management Studio

To use SQLCMD in SSMS, first, you must enable the SQLCMD mode. To do that, select Query from the menu and select SQLCMD Mode, as shown in the following image:

If you want to set the SQLCMD mode by default, go to Tools → Options. In Options, select Query execution → SQL Server → General and select the By default, open new queries in SQLCMD mode checkbox.

Now, let us see how to use it.

For example, I want to get the total records of the Posts table of the Stackoverflow2010 database. To do that, the query should be written as follows:

:SETVAR TABLENAME "Posts"

:SETVAR DATABASENAME "Stackoverflow2010"

use $(DATABASENAME);

select count(1) from $(TABLENAME);

GO

  

Now, let’s run the query. The following screenshot shows the query output.

Now, let us see how to use SQLCMD in PowerShell.

Using SQLCMD in PowerShell

You can invoke SQLCMD using PowerShell. To do that, you must install PowerShell for SQL Server. You can read this article to learn more about PowerShell for SQL Server and how to install it.

Let us take a simple example. Suppose I want to get the list of stored procedures of the WideWorldImporters database. The PowerShell command is as follows:

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

  

Here is the output.

Another example shows how to export the output of an SQL script to a text file using sqlps. Suppose we want to export a list of SQL Server agent jobs. I have created a script named SQLJobs.sql which retrieves the list of SQL jobs. The script contains the following T-SQL command:

use [msdb]

go

select name, description,date_created from Sysjobs

  

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

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

  

Once the command is completed, I open the output file, which looks as follows.

Advanced SQLCMD techniques

Here are a few advanced techniques that can help you use SQLCMD more effectively. I am going to explain them with simple examples.

Example 1: Show error messages according to the error severity level

This example shows how to display an error message according to its severity level. This method can be used by adding the -m option. Suppose you are running a SELECT query against a non-existing database object. The command will return “Invalid object”, and the severity level of that error is 16. See the following screenshot.

Let’s take a look at an error that has a severity level of 15 – a syntax error

As you can see in the above screenshot, the error severity is 15, therefore SQLCMD did not show any error.

Example 2: Exit the SQLCMD session when an error occurs

This example shows how to exit your SQLCMD session when a command or query encounters an error. To do that, you must specify the -b option. Suppose you want to exit SQLCMD when the query encounters a “database does not exist” error.

Example 3: Accept user input

This example shows how to accept user input while executing a T-SQL script. This involves scripting variables in SQLCMD. To demonstrate that, I have created a script that populates the formal name of the country. The script uses the WideWorldImporters database and the application.Countries table. The content of the script is as follows:

use [WideWorldImporters]

Go

select CountryName, FormalName from application.countries where CountryName=$(CountryName)

Go

  

Now I save the script and execute it using the following SQLCMD command:

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

  

Here is the output.

Conclusion

In this article, you have learned about the SQLCMD command and how to use it with various examples. SQLCMD is a powerful tool that can help you run scripts, export your output to various files, and administer SQL Server. You can also use the DAC (Dedicated Admin Connection), which helps access damaged or corrupted database servers.

Finally, you can always power up the stock capabilities of SSMS with intelligent code completion and formatting, source control, unit testing, command-line automation, and plenty of other useful stuff available in bundles like dbForge SQL Tools.

New users can give them a free test drive for a whopping 30 days. Once you install the bundle, all the add-ins will be conveniently available from both the SSMS menu and Object Explorer. They save me so much time that I can’t help but recommend them.