Microsoft recently released an interactive, cross-platform command line query tool called mssql-cli. This tool provides a great enhancement over SQLCMD due to its features and ease of use. In this article, we will see how to install and use mssql-cli on Ubuntu.

Installation

To install mssql-cli on Ubuntu, follow these steps:

1. Open the terminal.
2. Import the public repository GPG keys by running the following command:
   wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
3. Register the Microsoft Ubuntu repository by running the following command:
   sudo curl -o /etc/apt/sources.list.d/microsoft.list https://packages.microsoft.com/config/ubuntu/16.04/prod.list
4. Update the list of products by running the following command:
   sudo apt-get update
5. Install mssql-cli by running the following command:
   sudo apt-get install mssql-cli

Usage

Once mssql-cli is installed, you can start using it by running the following command:
mssql-cli

Some of the important parameters for mssql-cli are:

• -S: SQL Server instance name or address
• -U: Username to connect to the database
• -P: Password
• -d: Database name to connect (if not specified, it connects to the master database)

For example, to connect to a SQL Server instance, you can use the following command:
mssql-cli -S 'SQL instance' -U 'user id' -P 'password'

Once connected, you can start writing queries with the help of T-SQL IntelliSense. As you start typing, mssql-cli will provide suggestions for completing the query. It also identifies the schema, table, views, and functions.

Mssql-cli provides output in a tabular format, making it easy to read the information. It also supports multiline queries, allowing you to write long queries that span multiple lines. To enable multiline support, press F3. In multiline mode, T-SQL intelligence works fine and provides suggestions as you type. To execute a query in multiline mode, it should end with a semicolon (;).

Mssql-cli is intelligent enough to help in writing join queries. You can select the required join columns using the arrow keys and press Enter. This will write the desired columns in the query, which can be executed.

In conclusion, mssql-cli is a powerful and user-friendly tool for interacting with SQL Server using the command line. It provides features like auto-completion, syntax highlighting, query history, and more. By following the installation and usage instructions in this article, you can easily install and start using mssql-cli on Ubuntu.