Using sqlcmd with Azure SQL

Introduction to sqlcmd

The sqlcmd utility is a command-line tool that allows you to submit T-SQL statements to SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics. It's a powerful tool for scripting, automating tasks, and interacting with your Azure SQL resources from the command line.

This guide will walk you through the essential steps of using sqlcmd to manage and query your Azure SQL databases.

Installation

sqlcmd is included as part of the Microsoft ODBC Driver for SQL Server. You can download and install it from the official Microsoft documentation. On Linux and macOS, it's often available through package managers.

  • Windows: Download the SQL Server Command Line Utilities from the Microsoft Download Center.
  • Linux (Ubuntu/Debian): 
    sudoapt-get install mssql-tools
# Or for newer versions:
sudo apt-get install mssql-tools unixodbc-dev
  • Linux (RHEL/CentOS/Fedora): 
    sudoyum install mssql-tools
  • macOS: 
    brew install mssql-tools

Ensure the /opt/mssql-tools/bin (Linux) or equivalent directory is in your system's PATH environment variable.

Basic Usage

The fundamental syntax for sqlcmd is:

sqlcmd [options] [query]

Where options are command-line flags that configure the connection and behavior, and query is an optional T-SQL statement to execute.

Connecting to Azure SQL

To connect to your Azure SQL Database or Managed Instance, you'll typically use the following options:

  • -S: Server name (e.g., yourserver.database.windows.net)
  • -d: Database name
  • -U: Username
  • -P: Password (use with caution, or omit to be prompted securely)
  • -E: Use trusted authentication (if applicable, less common for Azure SQL)
  • -N: Enable encryption (recommended for Azure SQL)
  • -C: Trust server certificate (use only if you understand the security implications)

Example Connection:

sqlcmd -S yourserver.database.windows.net -d AdventureWorks -U myadmin -P MyComplexPassword123! -N

For Azure SQL Managed Instance, the server name will be different, often using a DNS name specific to your VNet.

Executing T-SQL Commands

You can execute single commands directly using the -Q option:

sqlcmd -S yourserver.database.windows.net -d master -U myadmin -Q "SELECT @@VERSION;"

Alternatively, you can enter interactive mode by omitting the query and starting sqlcmd with connection parameters:

sqlcmd -S yourserver.database.windows.net -d AdventureWorks -U myadmin

You will then see a 1> prompt where you can type your T-SQL statements. Type GO on a new line to execute the batch.

Scripting with Input Files

sqlcmd excels at running T-SQL scripts from files. Use the -i option:

sqlcmd -S yourserver.database.windows.net -d AdventureWorks -U myadmin -i my_script.sql

You can also redirect output to a file using the -o option:

sqlcmd -S yourserver.database.windows.net -d AdventureWorks -U myadmin -Q "SELECT * FROM dbo.Products;" -o products_output.txt

Advanced Options

  • -h: Set the number of header characters (default is 15).
  • -s: Column separator character (default is space).
  • -w: Set the screen width.
  • -W: Remove trailing spaces from column values.
  • -y: Set the display width of a variable-length column.
  • -Y: Set the display width of a fixed-length column.
  • -r [1|0]: Send error messages to stderr (1) or stdout (0).
  • -a packetsize: Request a specific network packet size.
  • -k: Remove all spaces from the data value.

Refer to the official Microsoft documentation for a complete list of options.

Practical Examples

1. Get server version:

sqlcmd -S yourserver.database.windows.net -d master -U myadmin -Q "SELECT @@VERSION;"

2. List all tables in a database:

sqlcmd -S yourserver.database.windows.net -d AdventureWorks -U myadmin -Q "SELECT name FROM sys.tables;" -s "," -W

3. Create a new table (from a script file):

Create a file named create_table.sql with:

USE AdventureWorks;
CREATE TABLE dbo.SampleTable (
    ID INT PRIMARY KEY,
    Name VARCHAR(50)
);
GO

Then run:

sqlcmd -S yourserver.database.windows.net -d AdventureWorks -U myadmin -i create_table.sql