SQL Server has one of the best management tools named SQL Server Management Studio (a.k.a. SSMS). It offers a lot of features that make the life of developers and DBAs much easier. But sometimes, there are some issues that cannot be fixed using SQL Server Management Studio. Especially when it comes to running ad-hoc SQL scripts or recovering crashed SQL Server instances. In such use cases, you can use SQLCMD.

This is a sponsored article by Devart. Devart is currently one of the leading providers of database management software and ALM solutions for the most popular database servers.

Getting started with SQLCMD

In this article, we will learn about SQLCMD. It is a command-line tool that can be used for the following tasks:

Run ad-hoc SQL queries and stored procedures on local and remote servers

Export SQL query output to text or CSV files

Manage and administer SQL Server instances and databases on Windows and Linux

To make query writing in SSMS faster and easier, as well as to enhance it with additional features for database management and administration, we enhanced it with dbForge SQL Tools, a pack of add-ins seamlessly integrated into SSMS.

Now let us begin with installation. 

To install the SQLCMD utility, you need to select the native SQL Server client tools while installing SQL Server. You can also install it separately using the SQL Server installation manager.

The SQLCMD utility can be invoked by just typing SQLCMD in PowerShell or in the command prompt. You can see the list of options that can be used with SQLCMD by running the following command:

PS C:\Users\nisar> SQLCMD -?

This is what the command-line output looks like.

Connecting to SQL Server using SQLCMD

Now, let us understand how to connect to a SQL Server instance using SQLCMD.

Example 1: Connect to the default SQL Server instance

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

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

As you can see, the command output is 1> which shows that you are connected to SQL Server.

Note that if you are connecting to the default instance of SQL Server on a local machine, you do not have to explicitly specify the hostname/server name.

Example 2: Connect to a named SQL Server instance

Now, let us check another example showing how to connect to a named SQL Server instance.

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

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

Here is the output.

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

Now, let us 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 want to use SQL Server authentication, you need to specify the -U (user name) and -p (password) options. If you do not specify the password, the SQLCMD utility will ask you to enter the password. The following screenshot illustrates this.

Working with SQLCMD in the interactive mode

In this section, we will see how to run SQLCMD in the interactive mode, execute SQL queries, and view the output. The interactive mode allows writing SQL statements and commands. Let’s start with learning how to connect to SQL Server, enter the interactive mode, and run 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 begins, run the following SQL query in the SQLCMD utility:

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 query output.

As you can see, the above query has populated the list of databases with database owners.

Example 2: Check the current database

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

Select DB_NAME()
Go

This is the query output.

The query has returned the master database because I have not set the default database for the login which I am using to connect to SQL Server.

Example 3: Execute SQL queries

You can run SQL queries using SQLCMD by specifying the -Q parameter. For example, you want to view the list of tables created in the SchoolManagement database using SQLCMD. The command should be written as follows:

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

Take a look at the query output.

Similarly, you can run other queries as well. Please note that the login you are using to connect to SQL Server must have the required permission on the database.

Working with SQLCMD in the command prompt

This is where we will see how to execute SQL scripts via the command prompt. This feature is useful when you want to run automation tasks, bulk operations, and long-running queries that do not require any user input.

I have created an SQL script that contains an SQL query that is used to populate the list of objects created in the WideWorldImporters database. The query is as follows:

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

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

For that, we’ll use the following options:

-o: Specify the destination file. In this demo, the destination text file is called WideWorldImportores_objects.txt.

-i: Specify the location of the SQL script. In this demo, the SQL script is called DBObjects.sql.

Now, let’s run the following command:

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

Once the command is successfully completed, it’s time to review the text file.

As you can see in the above screenshot, the query has been executed successfully.

Now, let us take another example. Here we will learn how to generate a backup of StackOverflow2010 using a SQL script. The query to generate a backup is as follows:

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

I have stored the 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.

As you can see, the query returned the formal name India.

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.

Source link