SQLCMD完全指南：掌控 SQL Server

作者：劉濤 2024-06-19 08:29:28

SQL Server 擁有被廣泛認可的一流管理工具——SQL Server Management Studio（簡稱 SSMS）。它提供了豐富的功能，極大地簡化了開發人員和數據庫管理員（DBA）的工作。

譯者 | 劉濤

審校 | 重樓

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

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

SQLCMD 入門

在這篇文章中，我們將深入學習 SQLCMD。它是一個功能強大的命令行工具，可用于以下任務：

在本地和遠程服務器上運行臨時 SQL 查詢和存儲過程
將 SQL 查詢結果導出到文本或 CSV 文件
在 Windows 和 Linux 環境下管理和管控 SQL Server 實例和數據庫

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

現在，讓我們從 SQLCMD 的安裝開始。

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

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

PS C:\Users\nisar> SQLCMD -?

這是命令行執行后輸出的樣子：

使用 SQLCMD 連接 SQL Server

現在，讓我們了解如何使用SQLCMD連接到SQL Server實例。

示例1：連接到默認的SQL Server實例

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

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

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

請注意，如果你正在連接到本地計算機上的SQL Server默認實例，則無需明確指定主機名/服務器名。

示例2：連接到命名SQL Server實例

現在，我們來看另一個實例，演示如何連接到特定命名的SQL Server實例。

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

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

輸出則是：

示例3：使用Windows身份驗證和SQL Server身份驗證連接到SQL Server

現在，讓我們看看如何使用Windows和SQL Server身份驗證連接到SQL Server。

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

SQLCMD 交互模式操作

在本節中，我們將探討如何在交互模式下運行SQLCMD、執行SQL查詢以及查看輸出。交互模式允許編寫SQL語句和命令。讓我們從學習如何連接到SQL服務器、進入交互模式以及在SQLCMD中運行查詢開始。

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

首先，使用以下命令連接到你的數據庫服務器：

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

當交互式會話開始后，在SQLCMD實用程序中運行以下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;

以下是查詢輸出結果：

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

示例2：檢查當前數據庫

首先，連接到數據庫服務器并執行以下查詢：

Select DB_NAME()
Go

以下是查詢輸出結果：

查詢返回到主數據庫,因為我使用當前登錄連接到SQL Server時沒有設置默認數據庫。

示例3：執行SQL查詢

你可以通過指定-Q參數使用SQLCMD運行SQL查詢。例如，你想使用SQLCMD查看在SchoolManagement數據庫中創建的表列表。該命令應編寫如下：

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

查看輸出結果：

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

在命令提示符中使用 SQLCMD

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

首先，我已經創建了一個SQL腳本，其中包含一個SQL查詢，該查詢用于獲取在WideWorldImporters數據庫中創建的對象列表。該查詢內容如下：

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

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

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

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

現在，讓我們執行以下命令：

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

命令成功執行后，你可以查看文本文件輸出的內容：

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

接下來，我們將通過另一個示例展示來學習如何使用SQL腳本生成StackOverflow2010的備份。生成備份的查詢如下：

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

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

截圖1：

從上述截圖可以看出，備份已經生成。

截圖2：

在 SQL Server Management Studio 中使用 SQLCMD

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

如果你希望默認情況下啟用SQLCMD模式，請轉到“工具”→“選項”。在“選項”對話框中，選擇“查詢執行”→“SQL Server”→“常規”，并勾選“默認情況下，在新查詢中打開SQLCMD模式”復選框。

現在，讓我們看看如何使用它。

例如，我想獲取Stackoverflow2010數據庫中Posts表的總記錄數。查詢應編寫如下：

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

現在，我們運行查詢。以下截圖顯示了查詢輸出的內容：

現在，讓我們看看如何在PowerShell中使用SQLCMD。

在 PowerShell 中使用 SQLCMD

你可以使用PowerShell調用SQLCMD。要實現這一點，你需要先為SQL Server安裝PowerShell。你可以閱讀相關文章，了解有關SQL Server的PowerShell及其安裝的更多信息。

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

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

執行結果如圖所示：

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

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

為了運行該腳本，我在SQL Server的PowerShell中執行以下命令：

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

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

高級 SQLCMD 技巧

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

示例1:根據錯誤嚴重級別顯示錯誤消息

該示例展示了如何根據錯誤的嚴重級別來顯示相應的錯誤消息。可以通過添加-m參數來啟用此功能。假設你嘗試對一個不存在的數據庫對象執行SELECT查詢,通常這將返回"Invalid object"(無效對象)的錯誤信息,其嚴重級別為16。結果如下：

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

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

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

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

示例3:接受用戶輸入

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

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

現在，我保存腳本并使用以下SQLCMD命令執行它：

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

輸出結果如下：

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

結論

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

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

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

譯者介紹

劉濤，51CTO社區編輯，某大型央企系統上線檢測管控負責人。

原文標題：The Ultimate Guide to Navigating SQL Server With SQLCMD，作者：Nisarg Upadhyay

責任編輯：姜華來源： 51CTO