譯者 | 劉濤

審校 | 重樓

目錄

• SQLCMD 入門
• 使用 SQLCMD 連接 SQL Server
• SQLCMD 交互模式操作
• 在命令提示符中使用 SQLCMD
• 在 SQL Server Management Studio 中使用 SQLCMD
• 在 PowerShell 中使用 SQLCMD
• 高級 SQLCMD 技巧
• 結(jié)論

SQL Server 擁有被廣泛認(rèn)可的一流管理工具——SQL Server Management Studio（簡稱 SSMS）。它提供了豐富的功能，極大地簡化了開發(fā)人員和數(shù)據(jù)庫管理員（DBA）的工作。然而，有時候也會遇到一些難以通過 SSMS 解決的問題，尤其是在執(zhí)行臨時 SQL 腳本或恢復(fù)崩潰的 SQL Server 實(shí)例時。在這些場景下，SQLCMD 就成為了你的得力工具。

SQLCMD 入門

在這篇文章中，我們將深入學(xué)習(xí) SQLCMD。它是一個功能強(qiáng)大的命令行工具，可用于以下任務(wù)：

• 在本地和遠(yuǎn)程服務(wù)器上運(yùn)行臨時 SQL 查詢和存儲過程
• 將 SQL 查詢結(jié)果導(dǎo)出到文本或 CSV 文件
• 在 Windows 和 Linux 環(huán)境下管理和管控 SQL Server 實(shí)例和數(shù)據(jù)庫

為了使 SSMS 中的查詢編寫更快速、更簡便，并為數(shù)據(jù)庫管理和操作提供額外功能，我們引入了 dbForge SQL Tools。這是一套無縫集成到 SSMS 中的插件，極大地增強(qiáng)了 SSMS 的能力。

現(xiàn)在，讓我們從 SQLCMD 的安裝開始。

要安裝SQLCMD實(shí)用程序，你需要在安裝SQL Server時選擇本機(jī)SQL Server客戶端工具。你還可以使用SQL Server安裝管理器單獨(dú)安裝它。

你只需在PowerShell或命令提示符中輸入SQLCMD即可調(diào)用SQLCMD實(shí)用程序。要查看可與SQLCMD一起使用的選項列表，請運(yùn)行以下命令：

PS C:\Users\nisar> SQLCMD -?

這是命令行執(zhí)行后輸出的樣子：

使用 SQLCMD 連接 SQL Server

現(xiàn)在，讓我們了解如何使用SQLCMD連接到SQL Server實(shí)例。

示例1：連接到默認(rèn)的SQL Server實(shí)例

要在本地計算機(jī)上連接到SQL Server，請使用以下SQLCMD命令：

C:\Users\nisar>sqlcmd -S Nisarg-PC

如你所見，命令輸出為1>，這表明你已連接到SQL Server。

請注意，如果你正在連接到本地計算機(jī)上的SQL Server默認(rèn)實(shí)例，則無需明確指定主機(jī)名/服務(wù)器名。

示例2：連接到命名SQL Server實(shí)例

現(xiàn)在，我們來看另一個實(shí)例，演示如何連接到特定命名的SQL Server實(shí)例。

要連接到命名的SQL Server實(shí)例，你需要指定參數(shù)-S（服務(wù)器名）。例如，如果你的服務(wù)器名稱是MyServer，命名實(shí)例是SQL2017，則使用SQLCMD連接到它的命令將是：

C:\>sqlcmd -S Nisarg-PC\SQL2019

輸出則是：

示例3：使用Windows身份驗(yàn)證和SQL Server身份驗(yàn)證連接到SQL Server

現(xiàn)在，讓我們看看如何使用Windows和SQL Server身份驗(yàn)證連接到SQL Server。

要使用SQLCMD連接到SQL Server，你可以選擇使用Windows身份驗(yàn)證或SQL Server身份驗(yàn)證。如果你想使用SQL Server身份驗(yàn)證，你需要指定`-U`（用戶名）和`-P`（密碼）選項。如果你沒有確認(rèn)密碼，SQLCMD工具會提示你輸入密碼。下面的截圖說明了這一點(diǎn)。

SQLCMD 交互模式操作

在本節(jié)中，我們將探討如何在交互模式下運(yùn)行SQLCMD、執(zhí)行SQL查詢以及查看輸出。交互模式允許編寫SQL語句和命令。讓我們從學(xué)習(xí)如何連接到SQL服務(wù)器、進(jìn)入交互模式以及在SQLCMD中運(yùn)行查詢開始。

示例1：填充一個包含數(shù)據(jù)庫及其所有者的列表

首先，使用以下命令連接到你的數(shù)據(jù)庫服務(wù)器：

C:\>sqlcmd -S Nisarg-PC -U sa -p

當(dāng)交互式會話開始后，在SQLCMD實(shí)用程序中運(yùn)行以下SQL查詢：

use master;
select a.name,b.name from sys.databases a inner join sys.server_principals b
on a.owner_sid=b.sid where a.name not in ('ReportServer','ReportServerTempDB')
and a.database_id>5;

以下是查詢輸出結(jié)果：

正如你所看到的,上述查詢已經(jīng)填充了包含數(shù)據(jù)庫及其所有者的數(shù)據(jù)庫列表。

示例2：檢查當(dāng)前數(shù)據(jù)庫

首先，連接到數(shù)據(jù)庫服務(wù)器并執(zhí)行以下查詢：

Select DB_NAME()
Go

以下是查詢輸出結(jié)果：

查詢返回到主數(shù)據(jù)庫,因?yàn)槲沂褂卯?dāng)前登錄連接到SQL Server時沒有設(shè)置默認(rèn)數(shù)據(jù)庫。

示例3：執(zhí)行SQL查詢

你可以通過指定-Q參數(shù)使用SQLCMD運(yùn)行SQL查詢。例如，你想使用SQLCMD查看在SchoolManagement數(shù)據(jù)庫中創(chuàng)建的表列表。該命令應(yīng)編寫如下：

C:\>sqlcmd -S Nisarg-PC -d SchoolManagement -Q "select name from sys.tables"

查看輸出結(jié)果：

同樣，你也可以運(yùn)行其他查詢。請注意，用于連接SQL服務(wù)器的登錄名必須具有數(shù)據(jù)庫所需的權(quán)限。

在命令提示符中使用 SQLCMD

在這里，我們將探討如何通過命令提示符執(zhí)行SQL腳本。當(dāng)你需要運(yùn)行自動化任務(wù)、批量操作以及長時間運(yùn)行且不需要用戶輸入的查詢時，此功能非常有用。

首先，我已經(jīng)創(chuàng)建了一個SQL腳本，其中包含一個SQL查詢，該查詢用于獲取在WideWorldImporters數(shù)據(jù)庫中創(chuàng)建的對象列表。該查詢內(nèi)容如下：

use [WideWorldImporters]
go
select name, type_desc, create_date from sys.objects where type_desc <>'SYSTEM_TABLE'

請將上述查詢添加到名為sp_get_db_objects.sql的SQL腳本中。接下來，我們將把查詢的輸出導(dǎo)出到一個名為database_objects.txt的文本文件中。

為此，我們將使用以下選項：

• -o：指定目標(biāo)輸出文件。在本例中為WideWorldImportores_objects.txt。
• -i：指定SQL腳本的位置。在本例中為DBObjects.sql。

現(xiàn)在，讓我們執(zhí)行以下命令：

sqlcmd -S Nisarg-PC -i D:\Scripts\DBObjects.sql -o D:\Scripts\WideWorldImportores_objects.txt

命令成功執(zhí)行后，你可以查看文本文件輸出的內(nèi)容：

正如你在上面的屏幕截圖中看到的那樣，查詢已成功執(zhí)行。

接下來，我們將通過另一個示例展示來學(xué)習(xí)如何使用SQL腳本生成StackOverflow2010的備份。生成備份的查詢?nèi)缦拢?/span>

use master
go
backup database [Stackoverflow2010] to disk ='D:\SQLBackups\Stackoverflow2010.bak' with compression, stats=5

我已經(jīng)將上述備份命令存儲在名為StackOverflow2010_backup_script.sql的SQL腳本中。要執(zhí)行此腳本，SQLCMD命令將如下所示：

截圖1：

從上述截圖可以看出，備份已經(jīng)生成。

截圖2：

在 SQL Server Management Studio 中使用 SQLCMD

要在SSMS（SQL Server Management Studio）中使用SQLCMD，首先必須啟用SQLCMD模式。為此，請從菜單中選擇“查詢”，然后選擇“SQLCMD模式”，如下所示：

如果你希望默認(rèn)情況下啟用SQLCMD模式，請轉(zhuǎn)到“工具”→“選項”。在“選項”對話框中，選擇“查詢執(zhí)行”→“SQL Server”→“常規(guī)”，并勾選“默認(rèn)情況下，在新查詢中打開SQLCMD模式”復(fù)選框。

現(xiàn)在，讓我們看看如何使用它。

例如，我想獲取Stackoverflow2010數(shù)據(jù)庫中Posts表的總記錄數(shù)。查詢應(yīng)編寫如下：

:SETVAR TABLENAME "Posts"
:SETVAR DATABASENAME "Stackoverflow2010"
use $(DATABASENAME);
select count(1) from $(TABLENAME);
GO

現(xiàn)在，我們運(yùn)行查詢。以下截圖顯示了查詢輸出的內(nèi)容：

現(xiàn)在，讓我們看看如何在PowerShell中使用SQLCMD。

在 PowerShell 中使用 SQLCMD

你可以使用PowerShell調(diào)用SQLCMD。要實(shí)現(xiàn)這一點(diǎn)，你需要先為SQL Server安裝PowerShell。你可以閱讀相關(guān)文章，了解有關(guān)SQL Server的PowerShell及其安裝的更多信息。

讓我們舉一個簡單的例子。假設(shè)我想獲取WideWorldImporters數(shù)據(jù)庫中存儲過程的列表。以下是PowerShell命令：

PS C:\WINDOWS\system32> invoke-sqlcmd -database wideworldimporters -query "select name from sys.procedures"

執(zhí)行結(jié)果如圖所示：

SQL Server中的PowerShell模塊(SQLPS)也提供了一種將SQL腳本輸出導(dǎo)入文本文件的方式。假設(shè)我們需要導(dǎo)出SQL服務(wù)器代理工作列表。我已經(jīng)創(chuàng)建了一個名為SQLJobs.sql的腳本,用于檢索SQL工作列表的相關(guān)信息。該腳本包含以下T-SQL命令:

use [msdb]
go
select name, description,date_created from Sysjobs

為了運(yùn)行該腳本，我在SQL Server的PowerShell中執(zhí)行以下命令：

invoke-sqlcmd -inputfile "D:\Scripts\SQLJobs.sql" | Out-File -FilePath "D:\Scripts\SQLJobs_List.txt"

命令完成后，我打開輸出文件，內(nèi)容如下圖所示：

高級 SQLCMD 技巧

SQLCMD提供了多種高級技巧,可以幫助用戶更有效地使用該工具。本文將通過簡單示例來闡釋其中一些高級用法。

示例1:根據(jù)錯誤嚴(yán)重級別顯示錯誤消息

該示例展示了如何根據(jù)錯誤的嚴(yán)重級別來顯示相應(yīng)的錯誤消息?？梢酝ㄟ^添加-m參數(shù)來啟用此功能。假設(shè)你嘗試對一個不存在的數(shù)據(jù)庫對象執(zhí)行SELECT查詢,通常這將返回"Invalid object"(無效對象)的錯誤信息,其嚴(yán)重級別為16。結(jié)果如下：

接下來,我們來研究一個嚴(yán)重級別為15(語法錯誤)的示例：

正如你在上面的截圖中看到的，當(dāng)錯誤嚴(yán)重級別為15的時候，SQLCMD沒有顯示任何錯誤。

示例2:遇錯即退出SQLCMD會話

本示例闡釋了如何在命令或查詢執(zhí)行過程中遇到錯誤時,退出SQLCMD會話。要啟用此功能,需要指定-b參數(shù)。假設(shè)你希望在查詢遇到"數(shù)據(jù)庫不存在"的錯誤時退出SQLCMD，如下圖所示：

示例3:接受用戶輸入

本示例闡釋了在執(zhí)行T-SQL腳本時如何接受用戶輸入。這需要在SQLCMD中編寫變量腳本。為了演示這一功能,我創(chuàng)建了一個腳本,用于填充某個國家的正式名稱。該腳本使用WideWorldImporters數(shù)據(jù)庫和application.Countries表。腳本的主要內(nèi)容如下:

use [WideWorldImporters]
Go
select CountryName, FormalName from application.countries where CountryName=$(CountryName)
Go

現(xiàn)在，我保存腳本并使用以下SQLCMD命令執(zhí)行它：

sqlcmd -S Nisarg-PC -v CountryName='India' -i D:\Scripts\Asia_Countries.sql

輸出結(jié)果如下：

如你所見，查詢返回了正式名稱India。

結(jié)論

通過本文的介紹,你已經(jīng)了解了SQLCMD命令的多種用法和示例應(yīng)用。SQLCMD是一款功能強(qiáng)大的工具,可用于執(zhí)行腳本、導(dǎo)出輸出到各種文件格式,以及管理SQL Server實(shí)例。另外,當(dāng)數(shù)據(jù)庫服務(wù)器出現(xiàn)損壞或無法訪問的情況時,你還可以利用專用管理連接(Dedicated Administrator Connection,簡稱DAC)來獲取訪問權(quán)限。

此外,我們建議你考慮使用第三方增強(qiáng)工具,如dbForge SQL Tools等插件包,來擴(kuò)展SQL Server Management Studio(SSMS)的基礎(chǔ)功能。這些插件可為SSMS提供諸如智能代碼補(bǔ)全、代碼格式化、源代碼控制、單元測試、命令行自動化等多種增強(qiáng)功能,極大地提高了開發(fā)效率。

DbForge SQL Tools為新用戶提供長達(dá)30天的免費(fèi)試用期。一旦你安裝了該插件包,所有增強(qiáng)功能都可以方便地在SSMS的菜單和對象資源管理器中直接訪問使用。根據(jù)我的實(shí)際使用體驗(yàn),這些增強(qiáng)工具節(jié)省了大量的開發(fā)時間,因此我向你強(qiáng)烈推薦使用。

譯者介紹

劉濤，51CTO社區(qū)編輯，某大型央企系統(tǒng)上線檢測管控負(fù)責(zé)人。

原文標(biāo)題：The Ultimate Guide to Navigating SQL Server With SQLCMD，作者：Nisarg Upadhyay