MSSQL-CLI: An Interactive Command Line SQL Server Query Tool

MSSQL-CLI or Microsoft SQL Server CLI is an open source cross-platform interactive command line query tool for SQL Server. This command line tool can be used as an enhanced alternative to SQLCMD. It is yet another open source initiative by Microsoft contributed to dbcli organization.

Features of MSSQL-CLI

  1. Open Source: It is an open source product. The source code is at GitHub.
  2. Cross Platform: You can install it on Windows, MacOS and Linux distributions like Ubuntu, Debian, CentOS, Red Hat, OpenSUSE and Fedora.
  3. Interactive: It is an interactive command line tool. It has the advanced user-friendly features like T-SQL intellisense, multi-line editing, syntax highlighting, formatting of results and configuration file support.

MSSQL-CLI Vs SQLCMD

MSSQL-CLI is and enhanced alternative to the existing command line utility SQLCMD. Let us see the major deference between these two tools.

#

MSSQL-CLI

SQLCMD

1 Open-source contribution from Microsoft, source code available at GitHub. Microsoft’s proprietary software.
2 Has user friendly interactive features like T-SQL intellisense, multi-line editing, syntax highlighting, formatting of results, etc… No such interactive features.
3 Developed as cross-platform utility. Recently Microsoft released Linux and MacOS versions of sqlcmd and bcp utility packaged together as SQL Server Command Line Tools.
4 This tool is still in it’s infancy stage and does not have all the command arguments available in SQLCMD. For example, there is no option to specify the output file (-o). It is matured and has more options.

Installing MSSQL-CLI On Windows

Requirements

  • Windows 8.1 or higher
  • Windows Server 2012 or higher
  • Python (Latest Version)

Installation Steps

For this demonstration, I’m using Windows Server 2016, SQL Server 2017, MSSQL-CLI 0.15.0  and Python 3.7.0.

  1. The first step is to install Python if it is not available in your workstation or serve. Windows by default wont have python in it. Unless you have already installed python manually, follow these steps to install python.
  2. Download latest version of python from https://www.python.org/downloads/ and double-click the exe to install it.
    MSSQL-CLI Install Python
  3. In the installation screen make sure to select Add Python … to PATH check box at the bottom before pressing the “Install Now” link. This is important for mssql-cli installation.
    MSSQL-CLI Install Python
  4. Now open the Windows command prompt.
  5. Enter the below command in the prompt and press enter. This will install mssql-cli tool.
    pip install mssql-cli
    

    MSSQL-CLI Installation

  6. Once installation of the mssql-cli tool and other dependent components are completed, there will be a success message.
    MSSQL-CLI Installation Successful
  7. To verify the installation, in the command prompt, just type in “mssql-cli” it will as for SQL Server’s user name. MSSQL-CLI Installation verification
  8. The interactive SQL tool is ready for using.

Using MSSQL-CLI

To understand the comments you can use in the tool, start with the help option. type in the below comment in the command prompt to list down all the comment options.

mssql-cli -h

MSSQL-CLI Help

From the help, you can understand the various arguments you can use in the tool, like the argument for server, database, user name, password, etc…

Connecting to SQL Server & Database

Below is the syntax of the comment to login to an SQL Server using SQL Server authentication and to a database. After entering the comment press enter to connect to the server.

mssql-cli -S [Server Name] -U [User Name] -P [Password] -d [Database Name]

Arguments Used

  • -S or –server: To add the server name.
  • -U or –username: To add the SQL Server authentication user name.
  • -P or –password: To add the password.
  • d or –database: To add a database name. If you are not not providing this argument, the tool will login to the default database for the user.

MSSQL-CLI Connection

Connect Using Windows Authentication

Alternatively, to connect to a server using windows authentication, use -E argument. Here is the syntax.

mssql-cli -E

In this example, I have not specified the server name or the database name. So the tool connects to the default local SQL Server instance and to the default database master. To change the working database to another just ise the SQL command USE <Database Name>.

MSSQL-CLI Change Database

Executing SQL Queries

Once you have connected to the server and database, you can type in the query. While typing you can notice the intellisense drop-down, syntax highlighting. To select an option from the intellisense drop down, use the up and down arrow and then use the enter button to select and come out of the drop down.

MSSQL-CLI Select Query

Once the query completed is executed by pressing enter, you can see the result appearing in table format.

MSSQL-CLI Result

Multi-Line Mode

To activate multi-line mode, press F3. At the bottom of the command prompt window, you can notice whether the multi-line mode in on or off. Once the multi-line mode is active, while writing a query, press enter to go to next line. To execute the query add semicolon (;) and press enter.

MSSQL-CLI Multi-Line

Related Article

Next Steps

 


Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.