SQLCMD is a powerful command line utility that allows you to execute Transact-SQL (T-SQL) commands, stored procedures, and SQL queries for Microsoft SQL Server. While SQL Server Management Studio (SSMS) is commonly used for database administration, SQLCMD offers some advantages in terms of speed, low CPU usage, and portability.

Connecting to a SQL Server Instance

To connect to a SQL Server instance using SQLCMD, you can use the following syntax:

sqlcmd -S <server_name>\<instance_name> -U <username> -P <password>

If you prefer to use Windows authentication, you can use the following command:

sqlcmd -S <server_name>\<instance_name> -E

Listing SQL Server Databases

To list all the databases on a SQL Server instance, you can use the following command:

sqlcmd -S <server_name>\<instance_name> -E -Q "SELECT name from sys.databases"

Connecting to a Specific Database

To connect to a specific database, you can use the following command:

sqlcmd -S <server_name>\<instance_name> -E -d <database_name>

Alternatively, once connected, you can use the USE statement followed by the name of the database:

USE <database_name>

Running SQLCMD in Batch Mode

You can run SQLCMD scripts via an executable batch file, which makes it easy to deploy the script on other environments. For example, you can create a batch file that creates a database, a table, and imports data from a CSV file. Here’s an example of how the batch file and SQL script would look:

@echo off

set /p DatabaseName="Enter the database name: "
set /p TableName="Enter the table name: "

sqlcmd -S <server_name>\<instance_name> -b -e -v Databasename="%DatabaseName%" -v TableName="%TableName%" -i createDB_importCSV.sql 2> error.log

if %errorlevel% neq 0 (
  echo There was an error running the script. Please check the error.log file for more information.
) else (
  echo The script ran successfully.
) pause

By executing the batch file, you can create a database, a table, and import data from a CSV file.

Viewing Database Table

To quickly connect to an existing database and view the table content using SQLCMD, you can create an SQL file and a batch file. Here’s an example:

@echo off

set /p DatabaseName="Enter the database name: "
set /p TableName="Enter the table name: "

sqlcmd -S <server_name>\<instance_name> -b -e -v DBname="%DatabaseName%" -v TableName="%TableName%" -i ViewTable.sql 2> error.log

if %errorlevel% neq 0 (
  echo There was an error running the script. Please check the error.log file for more information.
) else (
  echo The script ran successfully.
) pause

By executing the batch file, you can enter the database and table names and view the table content.

Deleting Tables and Databases

To delete a table, you can use the following command:

DROP TABLE TableName;

To delete an entire database, you can use the following command:

DROP DATABASE DatabaseName;

Conclusion

SQLCMD is a valuable tool for managing SQL Server instances without relying on SQL Server Management Studio. It allows you to create, update, and delete databases and tables, and it offers the flexibility of running scripts in batch mode. By leveraging SQLCMD, you can streamline your database administration tasks and improve efficiency.