Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Upgrading MSSQL-cli and new commands


By:   |   Last Updated: 2018-03-29   |   Comments   |   Related Tips: More > 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 			
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

administrator

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'.

upgrade

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.

> \? 			
command

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+			
importers

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
administrator

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			
importers

List all tables in a database with MSSQL-cli

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

> \lt			
warehouse

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+			
default

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

> \ll+ sa			
login

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			
notepad

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

query

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

select

Show function definition in MSSQL-cli

Use \sf command to get a function definition.

> \sf application.DetermineCustomerAccess			
definition

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:

query

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]
			
select

Verify the saved query using \n.

wide world importers

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

property

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


Last Updated: 2018-03-29


next webcast button


next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools