sqlcmd in SQL Server

The sqlcmd utility allows SQL to be run at the command prompt over ODBC (Version 17.3+ allows Azure connectivity).

This tool comes with SSMS, or separate Windows & Linux downloads are available using the above link.

If you already have SSMS installed, you can find the directory of sqlcmd using this Microsoft documentation link – search ‘sqlcmd’ after selecting your SQL Version when on the page.

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

A command prompt will, prompt!… and be within the folder it was prompted from. 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 using the 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 it’s a named instance of SQL Server you’re connecting to, the -S <server-name\instance-name> parameter is required)

First things I’ll check here are;
# @@SERVERNAME (Server Hostname).
# @@SERVICENAME (MSSQL Instance Name)
# @@VERSION (MSSQL Version)

Next, I’ll confirm which user I’m connected as using SUSER_NAME().

Now that the connectivity stuff is confirmed, we can have a look at what data is sitting on this SQL Server, first by querying sys.databases to see all databases.

Ohhh, Adventureworks! Let’s see some of those Dimension tables by querying sys.tables.

And when we’re done, the command to exit out of sqlcmd is… EXIT.

---