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
- Open Source: It is an open source product. The source code is at GitHub.
- Cross Platform: You can install it on Windows, MacOS and Linux distributions like Ubuntu, Debian, CentOS, Red Hat, OpenSUSE and Fedora.
- 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.
- 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.
- Download latest version of python from https://www.python.org/downloads/ and double-click the exe to install it.
- 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.
- Now open the Windows command prompt.
- Enter the below command in the prompt and press enter. This will install mssql-cli tool.
pip install mssql-cli
- Once installation of the mssql-cli tool and other dependent components are completed, there will be a success message.
- To verify the installation, in the command prompt, just type in “mssql-cli” it will as for SQL Server’s user name.
- 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
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.
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>.
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.
Once the query completed is executed by pressing enter, you can see the result appearing in table format.
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.
Related Article
Next Steps
- To read more about the usage of MSSQL-CLI, go to the user document.
- Read about other major SQL Server management and development tools.