SQLCMD is a command-line utility to connect and execute SQL queries from the command prompt. Using SQLCMD we can only connect to SQL Server instances. Importantly we can run ad-hoc as well as interactive script plus automating SQL scripts execution. This utility is pre-installed with SQL Server installation. Instead of relying on SSMS for quick tasks, we can use SQLCMD utility to execute SQL queries through ad-hoc statements or .SQL files.
SQLCMD Utility
1. Command-line utility to execute SQL scripts
2. Execute ad-hoc & interactive SQL script execution
3. Execute .SQL file queries on a database for automation
How to Launch SQLCMD Utility
As stated this utility is pre-installed with SQL Server installation. Under SQL Server root directly thus we can launch this from any path in the command prompt.
Go to Run > cmd > sqlcmd
SQLCMD – Help Options
Before starting up with SQLCMD it is essential to understand all the options within this utility. Understanding all the options will help to explore advanced features. You can use sqlcmd -h in the command prompt to see the following figure.
SQLCMD – Connect SQL Server Using Windows Authentication
Connecting to SQL Server instance using SQLCMD option. We have –S (Server Name) & -E (Trusted connection) switch for SQLCMD to connect to SQL Server. Using the following command you will be connected to an SQL Server instance. >1 indicates a successful connection to SQL instance using SQLCMD.
[ -S Server ]
[ -E Trusted Connection]
sqlcmd -S DESKTOP-4COH4E7\SQLSERVER2019 -E
SQLCMD – Connect SQL Server Using SQL Server Authentication
Connecting to SQL Server using SQLCMD with SQL Server authentication. By specifying SQL server username and password with instance name can connect to SQL Server.
[ -S Server]
[ -U login id]
[ -P password]
sqlcmd -S DESKTOP-4COH4E7\SQLSERVER2019 -U vishwanathdalvi -P YourPassword
SQLCMD – Execute Queries
We have learned how to connect to SQL Server instances using SQLCMD. Next we will learn to execute queries. Once >1 prompt is visible you can execute queries. Add GOkeyboard at end of the query to mark the end of the query.
Summary
In a nutshell, we have seen how to use SQLCMD utility to connect to SQL Server instance. Using Windows & SQL Server authentication connected to SQL Server. Executing queries through command prompt using SQLCMD utility, in next few posts we shall explore more on SQLCMD utility use.
Read more and browse through more posts related to SQL Server on Tech-Recipes.
1. How To Deploy ISPAC File & SSIS Package From Command Line
2. SSIS- How To Export & Import ISPAC File SSISDB – Visual Studio
3. How To Create Database Diagram In SQL Server SSMS
4. How To Index Computed Column In SQL Server
5. How To Use Computed Column In SQL Server
