Upgrading MSSQL-cli and new commands

By: Rajendra Gupta   |   Comments   |   Related: > Tools

Problem

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.

Solution

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 			

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 

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

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.

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			

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"			

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			

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

> \ls app			

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			

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

> \lt+ people			

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

> \ll			

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			

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.

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.

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
			

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

Next Steps

• Take a look at the MSSQL-cli installation guide for multiple operating systems
• Read about What's new in SQL Server 2017
• Read more SQL Server 2017 tips

About the author

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