Basics of Running T-SQL Statements from Command Line using SQLCMD

This article is about developing a basic understanding of sqlcmd utility to run T-SQL commands directly from the command prompt without the need of SSMS (SQL Server Management Studio).

The article also highlights the importance of using a lightweight sqlcmd utility to perform some basic database tasks that would otherwise require getting connected to the database through a pre-installed database tool such as SSMS (SQL Server Management Studio) or SSDT (SQL Server Data Tools) followed by getting it ready to run SQL scripts against the desired database(s).

The sqlcmd utility can be a real time-saver for database developers and DBAs since they can straight away run the required SQL scripts from the command-line.

About SQLCMD Utility

Let us begin by getting familiarised with the sqlcmd utility and its uses.

Simple Definition

The sqlcmd utility is a command line tool that lets you run T-SQL commands directly from the command prompt.

Microsoft Definition

According to the Microsoft documentation, the sqlcmd utility is a command-line utility for ad hoc, interactive execution of Transact-SQL statements and scripts and for automating Transact-SQL scripting tasks.

Pre-Requisites

In order to use the sqlcmd utility, the user must have basic understanding of how to create and run T-SQL scripts.

Uses of SQLCMD Utility

There are two main uses of sqlcmd:

• Basic uses
• Advanced uses

Basic Uses of SQLCMD

Some of the basic uses of the sqlcmd utility in light of the Microsoft documentation are as follows:

• The sqlcmd utility can run T-SQL statements (at the command prompt)
• The sqlcmd utility can execute user-defined or system procedures (at the command prompt)
• The sqlcmd utility can also run saved SQL scripts files (at the command prompt)
• The sqlcmd utility can connect to multiple SQL Server instances and run scripts
• The sqlcmd utility can send T-SQL statements output to a text file

Advanced Uses of SQLCMD

Apart from basic uses, sqlcmd has the following advanced uses:

• The sqlcmd utility can run database maintenance tasks
• The sqlcmd utility can automate database maintenance tasks
• The sqlcmd utility can automate T-SQL scripts on multiple instances

Using SQLCMD Mode in SSMS

The sqlcmd mode can be turned on in SSMS (SQL Server Management Studio) to run sqlcmd scripts due to a number of reasons including the following:

1. When you want to run T-SQL commands side by side with the Windows commands supported by sqlcmd utility
2. When you want to test run sqlcmd commands before putting them into action at the command prompt or saving in files or using for automation

Using SQLCMD Utility

The sqlcmd utility is activated by the following methods:

Quick Method

Type sqlcmd on the Windows search box and press Enter:

Please note that this method (typing sqlcmd only) works if you have an unnamed default SQL instance.

Alternative Method 1:

Another way to start the sqlcmd utility is to right-click the Windows icon and click Run and then type sqlcmd:

Please note this method (typing sqlcmd only) works if you have an unnamed default SQL instance, for named instance you have to specify the instance name.

Alternative Method 2:

You can also start the sqlcmd utility by first running the command prompt and then typing sqlcmd in the command prompt window and pressing ENTER:

Note: Please note that typing “sqlcmd” in the command prompt window only works if you have a default unnamed SQL instance installed, otherwise you have to specify the name of the SQL instance to get connected to it through sqlcmd.

Activating SQLCMD Utility

Using any of the above methods by typing sqlcmd will change the prompt showing 1> as follows:

The Meaning of “1>” in SQLCMD

You will instantly notice that the prompt has changed to 1>, which means you are now connected to the default instance of SQL Server.

Getting Default SQL Instance Version

The sqlcmd utility (as it takes T-SQL commands directly) can help developers and DBAs in finding out the current version of their default SQL Server instance.

You can quickly find out the current version of the default instance by typing the following configuration function followed by the GO statement:

1> SELECT @@VERSION
2> GO

A sample output is as follows:

This is really handy since you don’t need to do the following steps to find out the version:

1. Open SSMS (SQL Server Management Studio)
2. Click Databasesà System Databases à master
3. Right-click master and click New Query
4. Type SELECT @@VERSION in the query window
5. Press F5 to run the query

It can easily be observed that the sqlcmd approach is faster as compared to the SSMS (SQL Server Management Studio) method.

Remember to Use GO

You might have noticed that in the sqlcmd mode we are running T-SQL commands followed by Go statement.

Go serves as a batch terminator, which means all the queries before Go are executed in one go (at once) just like the way when we run multiple statements at once.

Let us give it a go by getting the version of the default instance and the language currently used by it followed by GO as follows:

1> SELECT @@VERSION
2> SELECT @@LANGUAGE
3> GO

Getting Help

You can get the sqlcmd help by going back to command prompt and typing the following in the command prompt window:

Sqlcmd -?

Connecting to Named SQL Instance

Sqlcmd is not limited to getting connected with a default unnamed SQL instance only.

Sqlcmd comes with a number of server options in which one such server option is -S to connect to any named SQL Server instance.

Using Server Option (-S)

For example, if we have a named instance called  .\SQLTAB then get a fresh start by closing the current window and opening the Run dialogue box and typing the following command:

Instance Name Check (@@SERVERNAME)

To confirm that you are connected to the named instance such ash .\SQLTAB, please use another handy configuration function @@SERVERNAME to check the name of the instance by typing the following code:

Computer Name with Instance Name

You can also connect to a named instance by specifying the computer using the –S server option by opening the Run dialogue and typing:

Sqlcmd –S H-PC\SQLTAB

In the above example, H-PC is the name of the computer and SQLTAB is the name of the SQL instance.

Setup Sample Database using SQLCMD

Let us now set up a sample SQL database using the sqlcmd utility.

Prepare the Setup Script

First of all, let us create a setup script for a sample database called University:

-- (1) Create University sample database
CREATE DATABASE University;
GO

USE University

-- (2) Create Course table
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_NAME='Course') 
DROP TABLE dbo.Course 

CREATE TABLE [dbo].[Course] (
    [CourseId] INT           IDENTITY (1, 1) NOT NULL,
    [Name]     VARCHAR (30)  NOT NULL,
    [Detail]   VARCHAR (200) NULL,
    CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED ([CourseId] ASC)
);

-- (3) Create Student table
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_NAME='Student') 
DROP TABLE dbo.Student 

CREATE TABLE [dbo].[Student] (
    [StudentId] INT           IDENTITY (1, 1) NOT NULL,
    [Name]      VARCHAR (30)  NULL,
    [Course]    VARCHAR (30)  NULL,
    [Marks]     INT           NULL,
    [ExamDate]  DATETIME2 (7) NULL,
    CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED ([StudentId] ASC)
);

-- (4) Populate Course table
SET IDENTITY_INSERT [dbo].[Course] ON
INSERT INTO [dbo].[Course] ([CourseId], [Name], [Detail]) VALUES (1, N'DevOps for Databases', N'This is about DevOps for Databases')
INSERT INTO [dbo].[Course] ([CourseId], [Name], [Detail]) VALUES (2, N'Power BI Fundamentals', N'This is about Power BI Fundamentals')
INSERT INTO [dbo].[Course] ([CourseId], [Name], [Detail]) VALUES (3, N'T-SQL Programming', N'About T-SQL Programming')
INSERT INTO [dbo].[Course] ([CourseId], [Name], [Detail]) VALUES (4, N'Tabular Data Modeling', N'This is about Tabular Data Modeling')
INSERT INTO [dbo].[Course] ([CourseId], [Name], [Detail]) VALUES (5, N'Analysis Services Fundamentals', N'This is about Analysis Services Fundamentals')
SET IDENTITY_INSERT [dbo].[Course] OFF

-- (5) Populate Student table
SET IDENTITY_INSERT [dbo].[Student] ON
INSERT INTO [dbo].[Student] ([StudentId], [Name], [Course], [Marks], [ExamDate]) VALUES (1, N'Asif', N'Database Management System', 80, N'2016-01-01 00:00:00')
INSERT INTO [dbo].[Student] ([StudentId], [Name], [Course], [Marks], [ExamDate]) VALUES (2, N'Peter', N'Database Management System', 85, N'2016-01-01 00:00:00')
INSERT INTO [dbo].[Student] ([StudentId], [Name], [Course], [Marks], [ExamDate]) VALUES (3, N'Sam', N'Database Management System', 85, N'2016-01-01 00:00:00')
INSERT INTO [dbo].[Student] ([StudentId], [Name], [Course], [Marks], [ExamDate]) VALUES (4, N'Adil', N'Database Management System', 85, N'2016-01-01 00:00:00')
INSERT INTO [dbo].[Student] ([StudentId], [Name], [Course], [Marks], [ExamDate]) VALUES (5, N'Naveed', N'Database Management System', 90, N'2016-01-01 00:00:00')
SET IDENTITY_INSERT [dbo].[Student] OFF
GO

Create the Setup Script File

Copy the entire script and save it to Notepad as SetupUniversityDatabase.sql file in the C:\ drive for testing purposes.

Server Option (-i)

We use server option – i to get the script input from a file using the sqlcmd utility.

Run Database Setup using SQLCMD Utility

Open the command prompt window and type the following:

Sqlcmd –S . –i c:\setupuniversitydatabase.sql

Where “.” (dot) is a default unnamed SQL instance here, which can be replaced by a named SQL instance.

Database Check

Type sqlcmd to connect to the default instance of SQL Server, then type the following code to check if the database has been successfully created or not:

Use University
SELECT c.CourseId, c.Name, c.Detail FROM Course c
SELECT s.StudentId, s.Name, s.Course, s.Marks, s.ExamDate FROM Student s
GO

The output shows that the database has been successfully set up through the sqlcmd utility:

Database Check-2

Open SSMS (SQL Server Management Studio) and connect to the default instance, expand Databases node to see a freshly created University database through the sqlcmd utility:

All done! You not only got familiar with basics but also successfully learned to setup a sample database using the sqlcmd utility without even opening SSMS (SQL Server Management Studio) which is very handy in scenarios where an infrastructure team has to quickly setup environment including sample databases.

In the next article, we are going to discuss slightly advanced uses of the sqlcmd utility.

Things to Do

Now that you are ready to run T-SQL scripts using the sqlcmd utility after going through this article, you can improve your skills further by trying the following things:

1. Please try to create the sample database SQLBookShop mentioned in my article excluding the stored procedures mentioned afterward using the sqlcmd utility.
2. Please try creating setup script for the SQLBookShop database including any one of the stored procedures mentioned in my article and run the script file through the sqlcmd utility.
3. Please try to run the stored procedure created in step 2 using the sqlcmd utility.

Useful tool:

dbForge Studio for SQL Server – powerful IDE for SQL Server management, administration, development, data reporting and analysis.