olly - Fotolia

Get started Bring yourself up to speed with our introductory content.

Quick start tips for using the new MSSQL-CLI SQL query tool

The new MSSQL-CLI command-line tool provides many enhancements over SQLCMD CLI that enable you to quickly write and run T-SQL queries across Linux, macOS and Windows.

While SQL Server itself has gone through innumerable changes and enhancements over the years, its command-line tools have essentially stayed the same since the old SQL Server 6.5 days. But as Microsoft expands its support of cross-platform tools, the company has released a new command-line query tool for SQL Server called MSSQL-CLI, which provides many significant enhancements over the old SQLCMD tool.

The venerable SQLCMD program has long been the primary command-line tool for running T-SQL commands. SQLCMD is used for both configuring SQL Server instances and developing application queries. It has also essentially remained unchanged.

With the release of SQL Server on Linux, Microsoft has started down a path to support multiple platforms with its command-line tool set. Microsoft initially made ports of SQLCMD and BCP (bulk copy program) for Linux and macOS, but these ports essentially functioned like the older Windows versions.

The new MSSQL-CLI is open sourced under the BSD 3-Clause License and is a contribution to the DBCLI organization. It uses the same microservices-based back end (sqltoolsservice) as the Visual Studio Code SQL extension and SQL Operations Studio.

Let's take a closer look at installing and running the new MSSQL-CLI tool.

Downloading and installing MSSQL-CLI and Python on Windows

The process for installing MSSQL-CLI on Windows vs. Linux is quite different. Like most SQL Server client tools, MSSQL-CLI is a separate download from the main SQL Server installation. Unlike many SQL Server tools, MSSQL-CLI is built using Python. You need to have Python installed on the target system before you can use MSSQL-CLI.

In contrast to macOS or Linux, Python is not typically installed on Windows. There are two separate Python installation packages: Python 3.6.5 and Python 2.7.14, both of which support MSSQL-CLI. The following listing illustrates how to install Python version 3.6.5 on Windows and how to use the pip command -- a tool for installing and managing Python packages -- to install MSSQL-CLI.

Python3-6-5.exe
pip install mssql-cli

Downloading and installing MSSQL-CLI and Python on macOS

Installing MSSQL-CLI on macOS is a bit easier, as Python 2.7 is normally preinstalled on the Mac operating system. To install MSSQL-CLI on macOS, run the following commands:

$ sudo easy_install pip
$ sudo pip install -- upgrade pip
$ sudo pip install mssql-cli -- ignore-installed six

Downloading and installing MSSQL-CLI and Python on Linux

Installing MSSQL-CLI on Linux is also typically quite easy, as most Linux distributions come with Python preinstalled. To install MSSQL-CLI on Ubuntu 16.04, run the following commands in the Bash shell:

$ sudo apt-get update & sudo apt-get install -y libunwind8 python-pip libicu52
$ sudo pip install -- upgrade pip
$ sudo pip install mssql-cli

You can see the complete list of installation instructions for the different supported versions of Linux in the GitHub installation guide.

Using MSSQL-CLI

MSSQL-CLI runs exactly the same way on all of the different supported platforms. Like the older SQLCMD tool, MSSQL-CLI is a command-line tool that enables you to execute T-SQL commands. Unlike SQLCMD, MSSQL-CLI provides many modern enhancements that make it easier and faster to write and run T-SQL queries.

MSSQL-CLI provides T-SQL IntelliSense statement completion, syntax highlighting, a multiline edit mode and automatic formatting of query results. Once MSSQL-CLI is installed, you can run it from the Windows command line or the Linux Bash shell with SQL Server authentication using the following command and authentication parameters:

mssql-cli -S <server name> -U <user name> -d <database name>

You can run MSSQL-CLI using Windows authentication with the following command:

mssql-cli -E

You can enter mssql-cli --help to see the complete list of command-line options for MSSQL-CLI. Figure 1 shows the new cross-platform MSSQL-CLI tool running from a Windows command shell.

A typical MSSQL-CLI query prompt
Figure 1: The MSSQL-CLI query prompt

Figure 1 illustrates the new MSSQL-CLI T-SQL IntelliSense support at work, prompting for the appropriate database name following the T-SQL USE statement.

You can move through the IntelliSense prompts by pressing Tab or the arrow keys. Pressing Enter selects the current item and returns to the command window. To end MSSQL-CLI, type the quit command and press Enter or press Ctrl + D.

MSSQL-CLI remembers the queries that you have previously executed and automatically displays them as you enter T-SQL commands, making it easy to rerun previous statements. By default, MSSQL-CLI runs in single-line mode.

In single-line mode, commands are executed when you finish typing the command and press Enter. If the T-SQL statement is long enough to continue on to the next line, then MSSQL-CLI fills in the line with dots until the next character is even with the beginning of the prompt.

In multiline mode, commands are not executed until you enter a semicolon (;) and press Enter. You can keep adding lines by pressing Enter. You can make changes to your query by using the arrow keys to move the cursor through the different lines of the multiline query.

MSSQL-CLI displays the results in either a neatly formatted vertical or horizontal list, depending on how many columns are returned by the query. If all the returned columns fit on the screen, then MSSQL-CLI formats the output horizontally, as shown in Figure 2.

The MSSQL-CLI output is horizontally formatted
Figure 2: The MSSQL-CLI horizontally formatted output

If there are too many columns to fit horizontally on the screen, then MSSQL-CLI will automatically format the results to appear vertically, as shown in Figure 3.

MSSQL-CLI auto-formats results vertically
Figure 3: MSSQL-CLI auto-formatted results -- vertically

As you can see in Figure 3, MSSQL-CLI formats the output data in a readable display showing the column names and the associated data for a single row vertically when the output is too wide to fit on the screen.

The -- More -- indicator at the bottom of the window indicates that there is more data to scroll through. Pressing the Enter key advances the output display one column at a time. Pressing the Spacebar advances the display by one row at a time. To end the output display and return to the MSSQL-CLI command prompt, you can press q or Ctrl + C.

Advanced MSSQL-CLI commands

MSSQL-CLI provides several other enhancements beyond the basic SQLCMD capabilities, including the ability to be configured with environment variables and the ability to execute a number of special commands that can list databases, save and run queries, and more.

Some of the more important environment variables include MSSQL_CLI_SERVER, MSSQL_CLI_DATABASE and MSSQL_CLI_ROW_LIMIT. MSSQL_CLI_SERVER is used to set a default SQL Server instance name or address. You can set environment variable MSSQL_CLI_DATABASE to use a default database. You can use MSSQL_CLI_ROW_LIMIT to apply a returned row limit or use 0 to disable the prompting.

You can see examples of setting these environment variables in the following listing:

set MSSQL_CLI_SERVER=localhost
set MSSQL_CLI_DATABASE=AdventureWorks2014
set MSSQL_CLI_ROW_LIMIT=20

If you are using macOS or Linux, then you need to use the export command instead of the set command.

MSSQL-CLI also provides a number of special commands that make it easier to create and run queries. Figure 3 shows a display of the list of special commands made using the \? command.

Figure 4: MSSQL-CLI special command help
Figure 4: MSSQL-CLI special command help

Figure 4 shows that these special commands all start with the backslash (\) character. Entering the backslash will display IntelliSense prompting to help you complete the command. For example, in the following listing, you can see how the \ sn command can be used to save a query called names, and then the \n command can be used to run the saved query.

Adventureworks2014> \sn names select LastName, FirstName, Title from Person.Person
Adventureworks2014> \n names

This was last published in August 2018

Dig Deeper on Microsoft SQL Server Installation

Join the conversation

2 comments

Send me notifications when other members comment.

Please create a username to comment.

On which platforms do you plan to use MSSQL-CLI?
Cancel
MSSQL-CLI displays the results in either a neatly formatted vertical or horizontal list, depending on how many columns are returned by the query. If all the returned columns fit on the screen, then MSSQL-CLI formats the output horizontally, as shown in Figure 2...

Is there any command to see the result always on horizontal format??
Cancel

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close