Upgrading MSSQL-cli and new commands

By:   |   Comments   |   Related: > Tools


In my previous tip, new interactive command line tool MSSQL-cli for SQL Server, we explored the installation of Python, MSSQL-cli and its important features such as auto-completion, syntax highlighting, auto escaping, join suggestions, etc. Recently Microsoft released the first major update for MSSQL-cli since the Public Preview announcement in December. In this tip, we will explore how to upgrade MSSQL-cli and cover some of the new features.


We installed MSSQL-cli in my previous tip. If we connect to the database instance using MSSQL-cli, we can see that the previous MSSQL-cli version is 0.9.0.  Connect to the server as follows:

> mssql-cli -S .\sqlexpress -U sa 			
command prompt

Upgrading MSSQL-cli

As discussed earlier, MSSQL-cli is based on Python; it uses a preferred installer program (pip) to install MSSQL-cli. In order to upgrade MSSQL-cli we have two options:

  • Uninstall MSSQL-cli and then reinstall
  • Upgrade MSSQL-cli

In this tip, we will cover upgrading MSSQL-cli since it this process automatically uninstalls and reinstalls the required components.

Launch command prompt as administrator and run the below command.

>pip install --upgrade mssql-cli 
command prompt

This starts downloading the latest version of MSSQL-cli, we can see below it is downloading MSSQL-cli version 0.10.0


Once the download is complete, it upgrades (uninstalls and installs) only the required components that are available for the upgrade. If any particular component is already on the latest version, we get the message 'Requirement already up-to-date'.


Now connect to the database instance, we can see now the MSSQL-cli version is 0.10.0

command prompt

Special commands in MSSQL-cli

MSSQL-cli introduced a set of special commands to perform quick tasks like running queries quickly. Once connected to the database instance, run the following command to get a list of the special commands.

> \? 			

Below is the list of special commands as shown above.

Command Description
\d List or describe database objects. Calls sp_help.
\dn Delete a named query.
\e Edit the query with an external editor.
\ld List databases.
\lf List functions.
\li List indexes.
\ll List logins and associated roles.
\ls List schemas.
\lt List tables.
\lv List views.
\n List or execute named queries.
\sf Show a function's definition.
\sn Save a named query.
help Show help.

Now let's explore some of the special commands.

List the databases in MSSQL-cli

Use the command \ld to get a list of the databases.

> \ld			
wide world importers

If we want to get basic details about databases, use the command \ld+.

> ld+			

List describing database object with MSSQL-cli

Use the command \d to get details about database objects.  This gives complete information about database objects. For example, below we can see:

  • Table owner, type, create date time
  • Column details along with properties
  • Identity column details
  • Index details
  • Constraints details
  • Foreign key details
> \d "Application"."Cities"			
command prompt

List all schemas in the database with MSSQL-cli

Use the command \ls to get a list of a database schema in a current database context.

> \ls			
command prompt

If we want to find the schema name that contains the word "app", we can run the below command.

> \ls app			
wide world

If we want further details about the schema, use the command /ls+.

> ls+ app			

List all tables in a database with MSSQL-cli

Use the \lt command to get a list of tables in the current database.

> \lt			

If we want to search for a specific keyword like "people" in the table, we can use a command like this:

> \lt people			
table schema

We can get more information by using \lt+ command.

> \lt+ people			
table catalog

To list logins, use the \ll command to get a list of logins and their type.

> \ll			
command prompt

For verbose output, use the command \ll+, it gives the default database name, login type and creates date as well.

> \ll+			

To filter the records for a specific login like "sa", run the below command.

> \ll+ sa			

Open Query Editor with MSSQL-cli

To edit the query using an external editor: use \e command. This opens up the Notepad editor as shown below.

> \e			

Now type the command in the Notepad editor (above the - #Type your query above this line) and save the file.


Now exit the Notepad editor, this will copy the text automatically into the MSSQL-cli window. Just press Enter to run the code.


Show function definition in MSSQL-cli

Use \sf command to get a function definition.

> \sf application.DetermineCustomerAccess			

Named Queries in MSSQL-cli

We can save queries in MSSQL-cli and call the name of the query for execution. SQL Server Management Studio allows saving the query as a .sql file however, we have to open it to execute it.

In MSSQL-cli, we do not need to type the complete code again. Just save the query and execute it.

The syntax for the named query is as below.

> \sn 'Name' Query			

In the below, we have saved the query as DBProperty.

> \sn DBProperty select name,collation_name,recovery_model_desc from sys.databases			
select name

We can see above that the query is saved now. To get a list of Named queries type \n as shown below:


To execute the query, simply run the following:

> \n DBProperty			

We can see the query executed with the expected output. This is a nice feature especially for frequently used queries.

world importers

Delete Named Queries in MSSQL-cli

Suppose we want to delete the previously saved named queries. We can use \dn to delete it.

First, let's save one more queries as a named query.

>\sn listofEmp SELECT TOP (10) [NationalIDNumber],[JobTitle],[BirthDate],[MaritalStatus],[Gender],[HireDate] FROM [AdventureWorks2017].[HumanResources].[Employee]

Verify the saved query using \n.

wide world importers

To delete, we can just use \dn and it automatically lists named query to select.


We can also pass the name of the query.  Suppose we want to delete the DBProperty named query, run the command \dn as follows.

> \dn DBProperty
db property

We can verify it has been deleted by using the \n command.

wide world importers
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Rajendra Gupta Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms