SQLCMD完全指南：掌控 SQL Server

译者 | 刘涛

审校 | 重楼

目录

• 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