sqlcmd in SQL Server

The sqlcmd utility allows Transact SQL to be run at the command prompt using ODBC to execute SQL batches for MS SQL Server.

This tool comes with SQL Management Studio, so if you have SSMS installed you can locate the sqlcmd exe file and call it via a Windows command terminal.

If you are running Linux, macOS or Docker, you can download sqlcmd and bcp from this Microsoft Documentation page (sqlcmd for linux).

Version notes for sqlcmd:
# Sqlcmd version 13.1 or higher is required for Always Encrypted (-g) and AAD Auth (-G)
# Microsoft ODBC Driver for SQL Server 17.6.1 or higher required for Azure Active Directory (AAD) Authentication and configure a Kerberos environment for macOS/Linux ODBC connectivity

The version information above may drift out of date – always look at Microsoft Docs for more up-to-date information.

In this blog post, I’m showing a demo of finding sqlcmd.exe on my local computer and then showing simple sqlcmd query examples.

– Find (or Download) SQLCMD.exe
– SQLCMD Query Examples
– Exit SQLCMD

Find (or Download) SQLCMD.exe

SQLCMD download (MS Docs) – link

SQLCMD and BCP are installed on your computer when you install SQL Server Management Studio. SSMS is the GUI application that most SQL Servers users use to query MSSQL, so there’s a good chance you might already have sqlcmd.

If you do already have SSMS installed, Microsoft has a documentation page for helping us find SQL file locations for each of the many apps. Search ‘sqlcmd’ after selecting your SQL version when on the page.

My sqlcmd.exe & bcp.exe files are in c:\program files\microsoft sql server\client sdk\odbc\130\tools\binn\

For quickness, we can type cmd.exe in the top address bar:

This will bring up a command prompt and the terminal will be as per the directory we executed this from in Windows Explorer. We don’t actually need CMD to be scoped into the same directory as sqlcmd.exe to use it though.

This command prompt is running as the User account I’m logged into Windows with. You can change which user runs CMD (shift-click app) to use another Windows account, or use -U / -P for SQL Server Authentication.

I’m running sqlcmd on the same machine as a default SQL Server instance, and my local user account has sysadmin permissions. That means I’m able to open sqlcmd without any parameters as shown below;

If connecting to a named instance of SQL Server we need to add the following parameter: -S <server-name\instance-name>

SQLCMD Query Examples

Now that we are in sqlcmd in the terminal window we can run any SQL statement our user permissions allow. This demo user is a member of the sysadmin role so can perform any action on the SQL Server instance.

I’m going to run a few commands to check basic SQL host and server information:
– @@SERVERNAME : Get SQL Server Hostname
– @@SERVICENAME : Get SQL Server Instance Name
– @@VERSION : Get SQL Server Product Version

Next, I will verify which user I’m connected to SQL Server as using SUSER_NAME().

Good, I know which server I’m on and who I am.

I now want to find out which databases are on this SQL Server. We can query sys.databases to list all databases on the SQL Instance.

There are 4x system databases on this SQL host and 1x User Database, Adventureworks. The famous Test database provided by Microsoft.

Lets now look at what tables are inside the Adventureworks database by querying sys.tables.

The above SQL commands we have run through here give us a view of what we take for granted in a GUI application. Now that we know the SQL Server host we are on and the databases/tables we can start on some ETL or admin work!

Exit SQLCMD

When you are done running queries in sqlcmd, you can exit and get back to terminal by typing and running exit