The sqlcmd tool in SQL Server 2005 uses

This article outlines the method procedures used by the SQLCMD tools in SQL Server 2005, and summarizes the list of variables.

1, you can use SQLCMD to perform interactive actions, such as:

C:sqlcmd>sqlcmd

1> SELECT name from sys.databases

2> Go

You can also try typing the following command, Real server list

1>:serverlist

SERVERS:

Wuyz

1> If you want to see the use of other commands, you can type: Help/?

2. Execute SQL script file

You can add parameter I to the sqlcmd command to execute the SQL script file, for example:

C:sqlcmd>sqlcmd-i Test.sql

The above I option allows you to execute a script file, and alternatively, you can specify the O option to export the output of the command to the specified file, and of course, we can specify the output in the code as follows

C:sqlcmd>sqlcmd

1>: Out Output.txt

2>: R Test.sql

3, the use of variables in the script

SQLCMD supports variable information that can be passed in to a user in a script, such as the following statement:

SET NOCOUNT on

Select $ (Cols) from $ (tablename)

Go

The above statement requests 2 parameters, which we can pass to the script by specifying the corresponding parameter information, as follows:

C:sqlcmd>sqlcmd-i Test.sql-o output.txt-v cols= "name,object_id,create_date" tablename= "sys.objects"

The purpose of the above statement is to execute the Test.sql script file, output the output to the OUTPUT.TXT file, and specify the Cols,tablename parameter values, respectively.

4, set the value of the variable in the script

In addition to passing the values of external parameters, you can set the value of the parameter internally, as in the following example

E:sqlcmdackuptemplate.sql

Use master

Backup database [$ (db)] to disk= ' $ (file)

E:sqlcmdackupsingle.sql

: Setvar DB msdb

: Setvar file C:empmsdb.bak

: R e:sqlcmdackuptemplate.sql

If you want to know, of course, what variables are defined, you can use: Listvar command to display. Summary of MAIN commands:

: R filename

: serverlist

: List

: Listvar

: Error filename | STDOUT | STDERR

: Out filename | STDOUT | STDERR

:P erftrace FileName | STDOUT | STDERR

: Connect server[instance] [timeout] [User_name[password]]

: On Error [Exit | ignore]

: SetVar Variable Value

: Help:xml on | Off

Summary of main environment variables:

-A Sqlcmdpacketsize

-D Sqlcmddbname

-H sqlcmdworkstation

-H Sqlcmdheaders

-L Sqlcmdlogintimeout

-M Sqlcmderrorlevel

-P Sqlcmdpassword

-S Sqlcmsserver

-S SQLCMDCOLSEP

-T Sqlcmdstattimeout

-U Sqlcmduser

-W Sqlcmdcolwidth