当前位置：Gxlcms > 数据库问题 > sqlcmd命令行操作sql server

sqlcmd命令行操作sql server

时间：2021-07-01 10:21:17 帮助过：4人阅读

12.0.2000.8 NT %当前版本为SQLserver2014 12.0% Copyright (c) 2014 Microsoft. All rights reserved. usage: Sqlcmd [-U login id] [-P password] [-S server] [-H hostname] [-E trusted connection] [-N Encrypt Connection][-C Trust Server Certificate] [-d use database name] [-l login timeout] [-t query timeout] [-h headers] [-s colseparator] [-w screen width] [-a packetsize] [-e echo input] [-I Enable Quoted Identifiers] [-c cmdend] [-L[c] list servers[clean output]] [-q "cmdline query"] [-Q "cmdline query" and exit] [-m errorlevel] [-V severitylevel] [-W remove trailing spaces] [-u unicode output] [-r[0|1] msgs to stderr] [-i inputfile] [-o outputfile] [-z new password] [-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit] [-k[1|2] remove[replace] control characters] [-y variable length type display width] [-Y fixed length type display width] [-p[1] print statistics[colon format]] [-R use client regional setting] [-K application intent] [-M multisubnet failover] [-b On error batch abort] [-v var = "value"...] [-A dedicated admin connection] [-X[1] disable commands, startup script, environment variables [and exit]] [-x disable variable substitution] [-? show syntax summary]

二、最常用的选项

服务器选项（-<span style="color: #000000;">S），用于标识 sqlcmd 连接到的 Microsoft SQL Server 实例。
身份验证选项（</span>-E、-U 和 -P），用于指定 sqlcmd 连接到 SQL Server 实例所使用的凭据。-<span style="color: #000000;">E 选项为默认选项，毋须指定。
输入选项（</span>-Q、-q 和 -<span style="color: #000000;">i），用于标识 sqlcmd 输入的位置。
输出选项 (</span>-o)，用于指定 sqlcmd 输出所在的文件。

三、常见用法

使用 Windows 身份验证连接到默认实例，以交互方式运行 Transact-<span style="color: #000000;">SQL 语句：
sqlcmd </span>-S <ComputerName><span style="color: #000000;">
上述示例中，未指定 </span>-<span style="color: #000000;">E，因为它是默认选项，而且 sqlcmd 使用 Windows 身份验证连接到默认实例。
使用 Windows 身份验证连接到命名实例，以交互方式运行 Transact</span>-<span style="color: #000000;">SQL 语句：
sqlcmd </span>-S <ComputerName>\<InstanceName> 或者 sqlcmd -S .\<InstanceName><span style="color: #000000;">
使用 Windows 身份验证连接到命名实例，并指定输入和输出文件：
sqlcmd </span>-S <ComputerName>\<InstanceName> -i <MyScript.sql> -o <MyOutput.rpt><span style="color: #000000;">
使用 Windows 身份验证连接到本地计算机上的默认实例，执行查询，并在查询运行完毕后使 sqlcmd 保持运行状态：
sqlcmd </span>-q <span style="color: #800000;">"</span><span style="color: #800000;">SELECT * FROM AdventureWorks2012.Person.Person</span><span style="color: #800000;">"</span><span style="color: #000000;">
使用 Windows 身份验证连接到本地计算机上的默认实例，执行查询，将输出定向到某个文件，并在查询运行完毕后使 sqlcmd 退出：
sqlcmd </span>-Q <span style="color: #800000;">"</span><span style="color: #800000;">SELECT * FROM AdventureWorks2012.Person.Person</span><span style="color: #800000;">"</span> -<span style="color: #000000;">o MyOutput.txt
使用 SQL Server 身份验证连接到命名实例，以交互方式运行 Transact</span>-<span style="color: #000000;">SQL 语句，并由 sqlcmd 提示输入密码：
sqlcmd </span>-U MyLogin -S <ComputerName>\<InstanceName>

四、交互用法

交互方式，在请在未使用 -Q、-q、-Z 或 -<span style="color: #000000;">i 选项指定任何输入文件或查询的情况下运行实用工具。
例如：sqlcmd </span>-S <ComputerName>\<InstanceName><span style="color: #000000;">
交互方式2个常用的命令
GO </span>+<span style="color: #000000;"> Enter : 将语句发送到SQLserver服务器并执行
Exit 或 QUIT : 退出sqlcmd命令行工作方式
:REST : 清除语句缓存，键入 </span>^C 将使 sqlcmd 退出,在发出 GO 命令后，还可以用 ^<span style="color: #000000;">C 停止语句缓存的执行。
:ED : 使用编辑器编写SQL
示例 
C:\</span>>sqlcmd -U sa -P Sqlserve -<span style="color: #000000;">H HQ1636
</span><span style="color: #800080;">1</span>><span style="color: #000000;"> use testdb;
</span><span style="color: #800080;">2</span>><span style="color: #000000;"> go
已将数据库上下文更改为 </span><span style="color: #800000;">‘</span><span style="color: #800000;">testdb</span><span style="color: #800000;">‘</span><span style="color: #000000;">。
</span><span style="color: #800080;">1</span>> <span style="color: #0000ff;">select</span> * <span style="color: #0000ff;">from</span><span style="color: #000000;"> t2;
</span><span style="color: #800080;">2</span>><span style="color: #000000;"> go
id id2 ename
</span>----------- ----------- -------------------
<span style="color: #800080;">1</span> <span style="color: #800080;">1</span><span style="color: #000000;"> NULL
</span><span style="color: #800080;">1</span><span style="color: #000000;"> NULL NULL
</span><span style="color: #800080;">1</span> <span style="color: #800080;">2</span><span style="color: #000000;"> John
(</span><span style="color: #800080;">3</span><span style="color: #000000;"> rows affected)
</span><span style="color: #800080;">1</span>> exit

五、使用sqlcmd运行SQL脚本
这个是比较管用的。对于熟悉Oracle SQL*Plus或者MySQL命令行的童鞋来说，有这个工具执行脚本，尤其是多个脚本需要执行的情绪，那个爽啊，不说了，直接看用法。

1、执行单个脚本

<span style="color: #000000;">脚本内容如下
C:\</span>><span style="color: #000000;">type E:\temp\Testsql.sql
USE testdb;
GO
SELECT </span>*<span style="color: #000000;"> FROM t2;
GO
执行脚本
C:\</span>>sqlcmd -U sa -P Sqlserve -H HQ1636 -i E:\temp\Testsql.sql -<span style="color: #000000;">o E:\temp\Testresult.txt
C:\</span>><span style="color: #000000;">type E:\temp\Testresult.txt
已将数据库上下文更改为 </span><span style="color: #800000;">‘</span><span style="color: #800000;">testdb</span><span style="color: #800000;">‘</span><span style="color: #000000;">。
id id2 ename
</span>----------- ----------- --------------------
<span style="color: #800080;">1</span> <span style="color: #800080;">1</span><span style="color: #000000;"> NULL
</span><span style="color: #800080;">1</span><span style="color: #000000;"> NULL NULL
</span><span style="color: #800080;">1</span> <span style="color: #800080;">2</span><span style="color: #000000;"> John
(</span><span style="color: #800080;">3</span> rows affected)

2、通过专用管理连接使用sqlcmd
下面使用专用连接方式杀死特定的session

C:\>sqlcmd -U sa -P Sqlserve -H HQ1636 -<span style="color: #000000;">A
</span><span style="color: #800080;">1</span>> SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id<><span style="color: #800080;">0</span><span style="color: #000000;">;
</span><span style="color: #800080;">2</span>><span style="color: #000000;"> go
blocking_session_id
</span>-------------------
<span style="color: #800080;">54</span><span style="color: #000000;">
(</span><span style="color: #800080;">1</span><span style="color: #000000;"> rows affected)
</span><span style="color: #800080;">1</span>> kill <span style="color: #800080;">54</span><span style="color: #000000;">;
</span><span style="color: #800080;">2</span>> go

3、使用 sqlcmd 执行存储过程

C:\><span style="color: #000000;">type E:\temp\TestProc.sql
CREATE PROC proc_query_t2 @ename VARCHAR(</span><span style="color: #800080;">20</span><span style="color: #000000;">)
AS
SELECT </span>*<span style="color: #000000;">
FROM t2
WHERE ename </span>=<span style="color: #000000;"> @ename;
GO
C:\</span>>sqlcmd -U sa -P Sqlserve -H HQ1636 -<span style="color: #000000;">i E:\temp\TestProc.sql
C:\</span>>sqlcmd -U sa -P Sqlserve -<span style="color: #000000;">H HQ1636
</span><span style="color: #800080;">1</span>><span style="color: #000000;"> :setvar ename robin
</span><span style="color: #800080;">1</span>><span style="color: #000000;"> exec testdb.dbo.proc_query_t2 $(ename)
</span><span style="color: #800080;">2</span>><span style="color: #000000;"> go
id id2 ename
</span>----------- ----------- --------------------
<span style="color: #800080;">1</span> <span style="color: #800080;">1</span><span style="color: #000000;"> Robin
(</span><span style="color: #800080;">1</span> rows affected)

4、使用 sqlcmd 进行数据库日常管理

C:\><span style="color: #000000;">type E:\temp\DB_bak.sql
USE master;
GO
BACKUP DATABASE [$(db)] TO DISK</span>=<span style="color: #800000;">‘</span><span style="color: #800000;">$(bakfile)</span><span style="color: #800000;">‘</span><span style="color: #000000;">
C:\</span>>sqlcmd -U sa -P Sqlserve -<span style="color: #000000;">H HQ1636
</span><span style="color: #800080;">1</span>><span style="color: #000000;"> :setvar db testdb
</span><span style="color: #800080;">1</span>><span style="color: #000000;"> :setvar bakfile e:\temp\testdb01.bak
</span><span style="color: #800080;">1</span>><span style="color: #000000;"> :r e:\temp\DB_bak.sql
已将数据库上下文更改为 </span><span style="color: #800000;">‘</span><span style="color: #800000;">master</span><span style="color: #800000;">‘</span><span style="color: #000000;">。
</span><span style="color: #800080;">1</span>><span style="color: #000000;"> go
已为数据库 </span><span style="color: #800000;">‘</span><span style="color: #800000;">testdb</span><span style="color: #800000;">‘</span>，文件 <span style="color: #800000;">‘</span><span style="color: #800000;">testdb</span><span style="color: #800000;">‘</span> (位于文件 <span style="color: #800080;">1</span> 上)处理了 <span style="color: #800080;">368</span><span style="color: #000000;"> 页。
已为数据库 </span><span style="color: #800000;">‘</span><span style="color: #800000;">testdb</span><span style="color: #800000;">‘</span>，文件 <span style="color: #800000;">‘</span><span style="color: #800000;">testdb_log</span><span style="color: #800000;">‘</span> (位于文件 <span style="color: #800080;">1</span> 上)处理了 <span style="color: #800080;">5</span><span style="color: #000000;"> 页。
BACKUP DATABASE 成功处理了 </span><span style="color: #800080;">373</span> 页，花费 <span style="color: #800080;">0.377</span> 秒(<span style="color: #800080;">7.729</span> MB/秒)。

5、sqlcmd 对多个实例执行代码

<span style="color: #800080;">2</span>> :connect <span style="color: #800080;">192.168</span>.<span style="color: #800080;">1.194</span> -U robin -<span style="color: #000000;">P xx
Sqlcmd: Successfully connected to server </span><span style="color: #800000;">‘</span><span style="color: #800000;">192.168.1.194</span><span style="color: #800000;">‘</span><span style="color: #000000;">.
</span><span style="color: #800080;">1</span>> <span style="color: #0000ff;">select</span><span style="color: #000000;"> getdate()
</span><span style="color: #800080;">2</span>><span style="color: #000000;"> go
</span>-----------------------
<span style="color: #800080;">2016</span>-<span style="color: #800080;">03</span>-<span style="color: #800080;">17</span> <span style="color: #800080;">13</span>:<span style="color: #800080;">31</span>:<span style="color: #800080;">16.390</span><span style="color: #000000;">
(</span><span style="color: #800080;">1</span><span style="color: #000000;"> rows affected)
</span><span style="color: #800080;">1</span>> :connect <span style="color: #800080;">192.168</span>.<span style="color: #800080;">1.207</span>,<span style="color: #800080;">2433</span> -U sa -P <span style="color: #800080;">123</span><span style="color: #000000;">
Sqlcmd: Successfully connected to server </span><span style="color: #800000;">‘</span><span style="color: #800000;">192.168.1.207,2433</span><span style="color: #800000;">‘</span><span style="color: #000000;">.
</span><span style="color: #800080;">1</span>> <span style="color: #0000ff;">select</span><span style="color: #000000;"> getdate()
</span><span style="color: #800080;">2</span>><span style="color: #000000;"> go
</span>-----------------------
<span style="color: #800080;">2016</span>-<span style="color: #800080;">03</span>-<span style="color: #800080;">17</span> <span style="color: #800080;">13</span>:<span style="color: #800080;">32</span>:<span style="color: #800080;">25.787</span><span style="color: #000000;">
(</span><span style="color: #800080;">1</span> rows affected)

6、使用批处理方式执行任务

<span style="color: #000000;">这个对于运维的童鞋来说实在是幸福，可以将脚本封装到批处理.bat文件以及加到windows计划任务。
C:\</span>><span style="color: #000000;">type e:\temp\batch.bat
@echo off
sqlcmd </span>-U sa -P Sqlserve -H HQ1636 -i e:\temp\all.sql -b -o e:\temp\<span style="color: #0000ff;">out</span><span style="color: #000000;">.log
C:\</span>><span style="color: #000000;">type e:\temp\all.sql
:r e:\temp\driver.sql
:r e:\temp\hostinfo.sql
C:\</span>><span style="color: #000000;">type e:\temp\hostinfo.sql
PRINT </span><span style="color: #800000;">‘</span><span style="color: #800000;">Below is host info.</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
PRINT </span><span style="color: #800000;">‘</span><span style="color: #800000;">=================================</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
USE [master];
GO
EXEC xp_msver;
GO
C:\</span>><span style="color: #000000;">type e:\temp\driver.sql
PRINT </span><span style="color: #800000;">‘</span><span style="color: #800000;">Below is drive info.</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
PRINT </span><span style="color: #800000;">‘</span><span style="color: #800000;">=================================</span><span style="color: #800000;">‘</span><span style="color: #000000;">;
USE master;
GO
EXEC xp_fixeddrives;
GO
C:\</span>>e:\temp\batch.bat %执行批处理脚本%<span style="color: #000000;">
Below </span><span style="color: #0000ff;">is</span><span style="color: #000000;"> drive info.
</span>=================================<span style="color: #000000;">
已将数据库上下文更改为 </span><span style="color: #800000;">‘</span><span style="color: #800000;">master</span><span style="color: #800000;">‘</span><span style="color: #000000;">。
drive MB 可用空间 
</span>----- -----------<span style="color: #000000;">
C </span><span style="color: #800080;">99784</span><span style="color: #000000;">
D </span><span style="color: #800080;">138623</span><span style="color: #000000;">
E </span><span style="color: #800080;">26783</span><span style="color: #000000;">
F </span><span style="color: #800080;">217172</span><span style="color: #000000;">
(</span><span style="color: #800080;">4</span><span style="color: #000000;"> rows affected)
Below </span><span style="color: #0000ff;">is</span><span style="color: #000000;"> host info.
</span>=================================<span style="color: #000000;">
已将数据库上下文更改为 </span><span style="color: #800000;">‘</span><span style="color: #800000;">master</span><span style="color: #800000;">‘</span><span style="color: #000000;">。
Index Name Internal_Value Character_Value 
</span>------ -------------------------------- -------------- --------------------------------------------------
<span style="color: #800080;">1</span><span style="color: #000000;"> ProductName NULL Microsoft SQL Server 
</span><span style="color: #800080;">2</span> ProductVersion <span style="color: #800080;">786432</span> <span style="color: #800080;">12.0</span>.<span style="color: #800080;">2000.8</span> 
<span style="color: #800080;">3</span> Language <span style="color: #800080;">2052</span><span style="color: #000000;"> 中文(简体，中国) 
</span><span style="color: #800080;">4</span><span style="color: #000000;"> Platform NULL NT x64 
</span><span style="color: #800080;">5</span><span style="color: #000000;"> Comments NULL SQL 
</span><span style="color: #800080;">6</span><span style="color: #000000;"> CompanyName NULL Microsoft Corporation 
</span><span style="color: #800080;">7</span> FileDescription NULL SQL Server Windows NT - <span style="color: #800080;">64</span><span style="color: #000000;"> Bit 
</span><span style="color: #800080;">8</span> FileVersion NULL <span style="color: #800080;">2014.0120</span>.<span style="color: #800080;">2000.08</span> ((SQL14_RTM).<span style="color: #800080;">140220</span>-<span style="color: #800080;">1752</span><span style="color: #000000;">) 
</span><span style="color: #800080;">9</span><span style="color: #000000;"> InternalName NULL SQLSERVR 
</span><span style="color: #800080;">10</span><span style="color: #000000;"> LegalCopyright NULL Microsoft Corp. All rights reserved. 
</span><span style="color: #800080;">11</span> LegalTrademarks NULL Microsoft SQL Server <span style="color: #0000ff;">is</span><span style="color: #000000;"> a registered trademark 
</span><span style="color: #800080;">12</span><span style="color: #000000;"> OriginalFilename NULL SQLSERVR.EXE 
</span><span style="color: #800080;">13</span><span style="color: #000000;"> PrivateBuild NULL NULL 
</span><span style="color: #800080;">14</span> SpecialBuild <span style="color: #800080;">131072008</span><span style="color: #000000;"> NULL 
</span><span style="color: #800080;">15</span> WindowsVersion <span style="color: #800080;">131072008</span> <span style="color: #800080;">6.1</span> (<span style="color: #800080;">7601</span><span style="color: #000000;">) 
</span><span style="color: #800080;">16</span> ProcessorCount <span style="color: #800080;">4</span> <span style="color: #800080;">4</span> 
<span style="color: #800080;">17</span><span style="color: #000000;"> ProcessorActiveMask NULL f 
</span><span style="color: #800080;">18</span> ProcessorType <span style="color: #800080;">8664</span><span style="color: #000000;"> NULL 
</span><span style="color: #800080;">19</span> PhysicalMemory <span style="color: #800080;">16297</span> <span style="color: #800080;">16297</span> (<span style="color: #800080;">17088618496</span><span style="color: #000000;">) 
</span><span style="color: #800080;">20</span> Product ID NULL NULL

————————————————
原文链接：https://blog.csdn.net/leshami/java/article/details/50913475

sqlcmd命令行操作sql server

标签：sele header intent serve form java acl 有一个 LEDE

sqlcmd命令行操作sql server

人气教程排行