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