Skip to content

Mssql-cli command line tool for SQL Server

    Mssql-cli is a new and interactive command line tool that provides the following key enhancements over sqlcmd in the Terminal environment:

    • T-SQL IntelliSense
    • Syntax highlighting
    • Pretty formatting for query results, including Vertical Format
    • Multi-line edit mode
    • Configuration file support

    Mssql-cli aims to offer an improved interactive command line experience for T-SQL. It is fully open source under the BSD-3 license, and a contribution to the dbcli organization, an open source suite of interactive CLI tools for relational databases including SQL Server, PostgresSQL, and MySQL. The command-line UI is written in Python and the tool leverages the same microservice backend (sqltoolsservice) that powers the VS Code SQL extension, SQL Operations Studio, and the other Python CLI tool we announced earlier, mssql-scripter.

    mssql-cli multi-line queries to build readable queries. Also includes syntax highlighting and “smart” auto-completion.

    In order to install mssql-cli, you must have Python on your machine. Please refer to the installation guide for OS-specific methods to get Python.

    Mssql-cli is installed via pip. If you know pip, you can install mssql-cli using command.

    $ pip install mssql-cli

    This command may need to run as sudo if you are installing to the system site packages. Mssql-cli can be installed using the –user option, which does not require sudo.

    $ pip install –user mssql-cli

    If you are having installation issues or would like more specific instructions, please see the installation guide.

    Once you have mssql-cli installed, connect to your database with the command:

    $ mssql-cli -S <server name> -U <user name> -d <database name>

    Use PowerShell on Windows to Manage SQL Server on Linux

    This article introduces SQL Server PowerShell and walks you through a couple of examples on how to use it with SQL Server on Linux. PowerShell support for SQL Server is currently available on Windows, MacOS, & Linux. This article walks you through using a Windows machine to connect to a remote SQL Server instance on Linux.

    Install the newest version of SQL PowerShell on Windows

    SQL PowerShell on Windows is maintained in the PowerShell Gallery. When working with SQL Server, you should always use the most recent version of the SqlServer PowerShell module.

    Launch PowerShell and import the sqlserver module

    Let’s start by launching PowerShell on Windows. Use Win+R, on your Windows computer, and type PowerShell to launch a new Windows PowerShell session.

    PowerShell

    SQL Server provides a PowerShell module named SqlServer. You can use the SqlServer module to import the SQL Server components (SQL Server provider and cmdlets) into a PowerShell environment or script.

    Copy and paste the following command at the PowerShell prompt to import the SqlServer module into your current PowerShell session:

    PowerShell

    Import-Module SqlServer

    Type the following command at the PowerShell prompt to verify that the SqlServer module was imported correctly:

    PowerShell

    Get-Module -Name SqlServer

    PowerShell should display information similar to the following output:

    ModuleType Version    Name          ExportedCommands

    ———- ——-    —-          —————-

    Script     21.1.18102 SqlServer     {Add-SqlAvailabilityDatabase, Add-SqlAvailabilityGroupList…

    Connect to SQL Server and get server information

    Let’s use PowerShell on Windows to connect to your SQL Server instance on Linux and display a couple of server properties.

    Copy and paste the following commands at the PowerShell prompt. When you run these commands, PowerShell will:

    • Display a dialog that prompts you for the hostname or IP address of your instance
    • Display the Windows PowerShell credential request dialog, which prompts you for the credentials. You can use your SQL username and SQL password to connect to your SQL Server instance on Linux
    • Use the Get-SqlInstance cmdlet to connect to the Server and display a few properties

    Optionally, you can just replace the $serverInstance variable with the IP address or the hostname of your SQL Server instance.

    PowerShell

    # Prompt for instance & credentials to login into SQL Server

    $serverInstance = Read-Host “Enter the name of your instance”

    $credential = Get-Credential

    # Connect to the Server and get a few properties

    Get-SqlInstance -ServerInstance $serverInstance -Credential $credential

    # done

    PowerShell should display information similar to the following output:

    Instance Name                   Version    ProductLevel UpdateLevel  HostPlatform HostDistribution               

    ————-                   ——-    ———— ———–  ———— —————-               

    your_server_instance            14.0.3048  RTM          CU13         Linux        Ubuntu

     Note

    If nothing is displayed for these values, the connection to the target SQL Server instance most likely failed. Make sure that you can use the same connection information to connect from SQL Server Management Studio.

    Using the SQL Server PowerShell Provider

    Another option for connecting to your SQL Server instance is to use the SQL Server PowerShell Provider. This provider allows you to navigate SQL Server instance similar to as if you were navigating the tree structure in Object Explorer, but at the cmdline. By default this provider is presented as a PSDrive named SQLSERVER:\ which you can use to connect & navigate SQL Server instances that your domain account has access to.

    You can also use SQL authentication with the SQL Server PowerShell Provider. To do this, use the New-PSDrive cmdlet to create a new PSDrive and supply the proper credentials in order to connect.

    In this example below, you will see one example of how to create a new PSDrive using SQL authentication.

    PowerShell

    # NOTE: We are reusing the values saved in the $credential variable from the above example.

    New-PSDrive -Name SQLonDocker -PSProvider SqlServer -Root ‘SQLSERVER:\SQL\localhost,10002\Default\’ -Credential $credential

    You can confirm that the drive was created by running the Get-PSDrive cmdlet.

    PowerShell

    Get-PSDrive

    Once you have created your new PSDrive, you can start navigating it.

    PowerShell

    dir SQLonDocker:\Databases

    Here is what the output might look like. You might notice the output is similar to what SSMS will display at the Databases node. It displays the user databases, but not the system databases.

    PowerShell

    Name                 Status           Size     Space  Recovery Compat. Owner

                                                Available  Model     Level

    —-                 ——           —- ———- ——– ——- —–

    AdventureWorks2016   Normal      209.63 MB    1.31 MB Simple       130 sa

    AdventureWorksDW2012 Normal      167.00 MB   32.47 MB Simple       110 sa

    AdventureWorksDW2014 Normal      188.00 MB   78.10 MB Simple       120 sa

    AdventureWorksDW2016 Normal      172.00 MB   74.76 MB Simple       130 sa

    AdventureWorksDW2017 Normal      208.00 MB   40.57 MB Simple       140 sa

    If you need to see all databases on your instance, one option is to use the Get-SqlDatabase cmdlet.

    Examine SQL Server error logs

    The following steps use PowerShell on Windows to examine error logs connect on your SQL Server instance on Linux. We will also use the Out-GridView cmdlet to show information from the error logs in a grid view display.

    Copy and paste the following commands at the PowerShell prompt. They might take a few minutes to run. These commands do the following:

    • Display a dialog that prompts you for the hostname or IP address of your instance
    • Display the Windows PowerShell credential request dialog, which prompts you for the credentials. You can use your SQL username and SQL password to connect to your SQL Server instance on Linux
    • Use the Get-SqlErrorLog cmdlet to connect to the SQL Server instance on Linux and retrieve error logs since Yesterday
    • Pipe the output to the Out-GridView cmdlet

    Optionally, you can replace the $serverInstance variable with the IP address or the hostname of your SQL Server instance.

    PowerShell

    # Prompt for instance & credentials to login into SQL Server

    $serverInstance = Read-Host “Enter the name of your instance”

    $credential = Get-Credential

    # Retrieve error logs since yesterday

    Get-SqlErrorLog -ServerInstance $serverInstance -Credential $credential -Since Yesterday | Out-GridView

    # done