SQL Server: учимся работать с SQLCMD

In the next example, we will show how to run a script using sqlcmd and show the results in another file.

We will first create a script file named columns.sql with the following sentences:

select * from adventureworks2021.information_schema.columns

In the cmd, run the following command to invoke sqlcmd:

sqlcmd -S DESKTOP-5K4TURFSQLEXPRESS -E -i c:sqlcolumns.sql -o c:sqlexit.txt

-i is used to specify the input. You specify the script file with the queries.
-o is used to show the results of the input in a file.

The exit.txt file will be created:

If we open the file, we will see the output results:

We will first create a script to back up the database named backup.sql:

In the cmd run the following command:

sqlcmd -S DESKTOP-5K4TURFSQLEXPRESS -E -i c:sqlbackup.sql -o
c:sqloutput.txt

The output will be similar to this one:

The commands will create a backup in a file named backup.sql in the c:sql folder:

You can work with variables in sqlcmd. The following example will set the variable DATABASENAME with the value adventureworks2021 and then we change the context to the database specified:

The result displayed is the following:

As you can see, SETVAR is used to specify the value of the variable. Then you need to use $() for the variable.

Another example is to set the variable CONTACTTYPEID to 3 and use it in the where clause to find a contact type ID according to the value of the variable:

The result displayed is the following:

You can list the tables of the database using the information_schema.tables view. We will first create a script named tables.sql. This script contains the tables and views:

Next, we will invoke sqlcmd to execute the script.

sqlcmd -E -i c:sqltables.sql -o c:sqloutput.txt -S DESKTOP-

5K4TURFSQLEXPRESS

The result displayed are the following in the output.txt file:

The following sentences will list the table names and the column names of a database in a script named columns.sql:

In the cmd run this command:

sqlcmd -E -i c:sqlcolumns.sql -o c:sqloutput.txt -S DESKTOP-

5K4TURFSQLEXPRESS

The result of the output.txt is the following:

You can check all the sqlcmd commands using this command:

Sqlcmd -?

This command will list all the commands available:

The following command will exit if it fails using the –b parameter:

sqlcmd -E -q “create table adventureworks” –b -S DESKTOP-

5K4TURFSQLEXPRESS

The command will exit if there is an error:

If there is an error, the error is displayed. However, according to the error level, you can stop this behavior by default using the -m option.

Here it is an example about this:

The following command shows an error message:

However, if you add the –m 16, the error will no longer be displayed because the error has the level of 15:

sqlcmd -E -q “create table adventureworks” -m 16 -S

DESKTOP-5K4TURFSQLEXPRESS

-m 16 will show only the errors higher than 16. As you can see the error message is no longer displayed

The following example will run a SQL script with one variable. The example will create a database specified by the user.

We will first create a script named createdb.sql with the following content:

Next, in the cmd we will run the database specifying the database name:

sqlcmd -E -v DATABASENAME=”Userinput” -i

c:sqlcreatedb.sql

The command will create a database named Userinput.

In sqlcmd you can run the sp_databases stored procedure:

And you will be able to see the database created: