SQLCMD: Execute SQL Commands & Scripts

The sqlcmd utility is a command-line tool provided by Microsoft for interacting with SQL Server. It allows users to execute SQL statements, scripts, and administrative commands directly from the command prompt. This guide provides essential examples for using sqlcmd effectively.

sqlcmd is invaluable for automating database tasks, running batch jobs, and performing quick queries without needing a full graphical interface. Its flexibility makes it a staple for database administrators and developers working with SQL Server.

Connecting to SQL Server

Establish a connection to your SQL Server instance. You can specify the server name, port, and authentication method.

# Connect to a database server with username
sqlcmd -S localhost,1433 -U username

# Connect to a database server with username, password, and specific database
sqlcmd -S localhost,1433 -U username -P password -d target_db

Managing Passwords

sqlcmd can be used to change user passwords securely.

# Change a password
sqlcmd -U username -P oldpassword -Z newpassword

Running SQL Scripts and Queries

Execute entire SQL scripts or single command-line queries. You can also pass variables to your scripts.

# Run a SQL script against a database with a variable
sqlcmd -S localhost,1433 -U username -v SOME_VARIABLE="foo" -i "/path/to/script.sql"

# Run a command-line query and exit immediately
sqlcmd -S localhost,1433 -U username -Q "INSERT QUERY HERE"

Checking Database Existence

A practical example demonstrating how to check if a database exists using a command-line query.

# Check if a database exists (returns 1 if True)
sqlcmd -S localhost,1433 -U username -h -1 -W -Q "SET NOCOUNT ON;SELECT 1 FROM sys.databases WHERE [Name] = N'YOURDBNAME'"

Further Resources

• SQLCMD Utility Documentation - Official Microsoft documentation for comprehensive usage.
• SQLCMD Questions on Stack Overflow - Community discussions and solutions for common issues.
• ISO/IEC 9075:2016 - SQL standard for understanding SQL syntax.