sqlcmd Examples (SQL Server)

The sqlcmd utility allows you to execute Transact-SQL commands directly from the command line using ODBC, enabling SQL Server batch execution without the need for a GUI. This post contains sqlcmd examples to help you understand it more.

sqlcmd is essential for running SQL queries, automating tasks, and managing SQL Server directly from the command line, offering speed and simplicity, especially in environments without a GUI.

Prerequisites

Windows Users:
If you have SQL Server Management Studio (SSMS) installed, sqlcmd.exe is likely already available.

Linux/macOS/Docker Users:
Download sqlcmd (and bcp) from the official Microsoft Documentation.

Version Notes

version 13.1 or higher is required for features like Always Encrypted (-g) and Azure Active Directory Authentication (-G).

The Microsoft ODBC Driver for SQL Server 17.6.1 or higher is needed for AAD and Kerberos authentication on macOS/Linux.

1. Finding or Downloading sqlcmd.exe

If SSMS is installed on your computer, sqlcmd is also installed too. We can find the application file in the following directory:

C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\<version>\Tools\Binn\

Replace your SQL version number with what’s installed your end. For example, 130 shown in the above screenshot is SQL Server 2016, if 2017 it’ll be 140.

You can open this .exe file or type/run sqlcmd within your terminal:

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

For more information, and if you are having issues connecting with sqlcmd you should look at the MS Docs.

2. Basic sqlcmd Query Examples

Once sqlcmd is accessible, you can connect to a SQL Server instance.

I’m going to get connected and check the logged-in user in this example:

-- Connect to the SQL Server instance  
sqlcmd -S <server-name> -U <username> -P <password>  

-- Get the SQL Server instance name  
SELECT @@SERVICENAME;  
GO  

-- Check the logged-in SQL Server user  
SELECT SUSER_NAME();  
GO  

Once connected, I can now list all databases on the SQL Server using the following query:

These queries help identify the SQL Server instance, confirm database access, and navigate to a specific database for additional tasks:

To exit sqlcmd, type EXIT and it enter.

Hope all this was useful!