By: Rajendra Gupta | Last Updated: 2018-03-29 | Comments | 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
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.
|\d||List or describe database objects. Calls sp_help.|
|\dn||Delete a named query.|
|\e||Edit the query with an external editor.|
|\ll||List logins and associated roles.|
|\n||List or execute named queries.|
|\sf||Show a function's definition.|
|\sn||Save a named query.|
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.
If we want to get basic details about databases, use the command \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.
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.
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.
For verbose output, use the command \ll+, it gives the default database name, login type and creates date as well.
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.
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.
- 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
Last Updated: 2018-03-29
About the author
View all my tips