数据库引擎脚本Database Engine Scripting

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse

SQL Server 数据库引擎SQL Server Database Engine 支持 MicrosoftMicrosoft PowerShell 脚本环境,以管理 数据库引擎Database Engine 的实例和这些实例中的对象。The SQL Server 数据库引擎SQL Server Database Engine supports the MicrosoftMicrosoft PowerShell scripting environment to manage instances of the 数据库引擎Database Engine and the objects in the instances. 还可以在与脚本环境非常类似的环境中生成并运行包含 数据库引擎Database Engine 和 Xquery 的 Transact-SQLTransact-SQL 查询。You can also build and run 数据库引擎Database Engine queries that contain Transact-SQLTransact-SQL and XQuery in environments very similar to scripting environments.

SQL Server PowerShellSQL Server PowerShell

SQL ServerSQL Server 包含两个可用来实现以下内容的 SQL ServerSQL Server PowerShell 管理单元:includes two SQL ServerSQL Server PowerShell snap-ins that implement:

  • SQL ServerSQL Server PowerShell 提供程序,它将 SQL ServerSQL Server 管理对象模型层次结构公开为类似于文件系统路径的 PowerShell 路径。A SQL ServerSQL Server PowerShell provider that exposes the SQL ServerSQL Server management object model hierarchies as PowerShell paths that are similar to file system paths. 可以使用 SQL ServerSQL Server 管理对象模型类来管理路径的每个节点处表示的对象。You can use the SQL ServerSQL Server management object model classes to manage the objects represented at each node of the path.

  • 一组执行 SQL ServerSQL Server 命令的 SQL ServerSQL Server cmdlet。A set of SQL ServerSQL Server cmdlets that implement SQL ServerSQL Server commands. 其中一个 cmdlet 是 Invoke-SqlcmdOne of the cmdlets is Invoke-Sqlcmd. 此 cmdlet 用于运行要与 数据库引擎Database Engine sqlcmd 实用工具一起运行的 查询脚本。This is used to run 数据库引擎Database Engine Query scripts to be run with the sqlcmd utility.

SQL ServerSQL Server 提供了用于运行 PowerShell 的以下功能:provides these features for running PowerShell:

  • 可导入到 PowerShell 会话中的 sqlps PowerShell 模块,该模块之后将加载 SQL ServerSQL Server 管理单元。可以交互方式运行即席 PowerShell 命令。The sqlps PowerShell module that can be imported to a PowerShell session, the module then loads the SQL ServerSQL Server snap-ins. You can interactively run ad hoc PowerShell commands. 可以使用诸如 .\MyFolder\MyScript.ps1 这样的命令来运行脚本文件。You can run script files using a command such as .\MyFolder\MyScript.ps1.

  • PowerShell 脚本文件可用作 SQL ServerSQL Server 代理 PowerShell 作业步骤的输入,这些步骤按预订的时间间隔或者作为对系统事件的响应来运行脚本。PowerShell script files can be used as input to SQL ServerSQL Server Agent PowerShell job steps that run the scripts either at scheduled intervals or in response to system events.

  • 用于启动 PowerShell 和导入 模块的 sqlps SQL ServerSQL Server 实用工具。The sqlps utility that starts PowerShell and imports the SQL ServerSQL Server module. 然后,您可以执行该模块支持的所有操作。You can then perform all actions supported by the module. 可以在命令提示符中启动 sqlps 实用工具,也可以通过在 SQL ServerSQL Server Management Studio 对象资源管理器树中右键单击节点并选择“启动 PowerShell”来启动 sqlps 实用工具。You can start the sqlps utility either in a command prompt or by right-clicking on the nodes in the SQL ServerSQL Server Management Studio Object Explorer tree and selecting Start PowerShell.

数据库引擎查询Database Engine Queries

数据库引擎Database Engine 查询脚本包含三种类型的元素:query scripts contain three types of elements:

  • Transact-SQLTransact-SQL 语言语句。language statements.

  • Xquery 语言语句。XQuery language statements

  • sqlcmd 实用工具中的命令和变量。Commands and variables from the sqlcmd utility.

SQL ServerSQL Server 提供了三种用于生成和运行 数据库引擎Database Engine 查询的环境:provides three environments for building and running 数据库引擎Database Engine queries:

  • 可以在 数据库引擎Database Engine 中的 数据库引擎Database Engine 查询编辑器中以交互方式运行和调试 SQL Server Management StudioSQL Server Management Studio查询。You can interactively run and debug 数据库引擎Database Engine queries in the 数据库引擎Database Engine Query Editor in SQL Server Management StudioSQL Server Management Studio. 可以在一个会话中编写并调试多个语句,然后将所有这些语句保存在一个脚本文件中。You can code and debug several statements in one session, then save all of the statements in a single script file.

  • 使用 sqlcmd 命令提示实用工具,可以交互方式运行 数据库引擎Database Engine 查询,还可以运行现有的 数据库引擎Database Engine 查询脚本文件。The sqlcmd command prompt utility lets you interactively run 数据库引擎Database Engine queries, and also run existing 数据库引擎Database Engine query script files.

数据库引擎Database Engine 查询脚本文件通常是使用 SQL Server Management StudioSQL Server Management Studio 查询编辑器在 数据库引擎Database Engine 中以交互方式进行编码的。query script files are typically coded interactively in SQL Server Management StudioSQL Server Management Studio by using the 数据库引擎Database Engine Query Editor. 之后,可在下面的某个环境中打开此文件:The file can later be opened in one of these environments:

  • 使用 SQL Server Management StudioSQL Server Management Studio 的“文件”/“打开”菜单,在新的数据库引擎Database Engine查询编辑器窗口中打开此文件。Use the SQL Server Management StudioSQL Server Management Studio File/Open menu to open the file in a new 数据库引擎Database Engine Query Editor window.

  • 通过 sqlcmd实用工具使用 -i input_file 参数运行此文件。Use the -iinput_file parameter to run the file with the sqlcmd utility.

  • 通过 PowerShell 脚本中的 Invoke-Sqlcmd cmdlet 使用 -QueryFromFile SQL ServerSQL Server 参数运行此文件。Use the -QueryFromFile parameter to run the file with the Invoke-Sqlcmd cmdlet in SQL ServerSQL Server PowerShell scripts.

  • 使用 SQL ServerSQL Server 代理 Transact-SQLTransact-SQL 作业步骤按计划的时间间隔或作为对系统事件的响应来运行脚本。Use SQL ServerSQL Server Agent Transact-SQLTransact-SQL job steps to run the scripts either at scheduled intervals or in response to system events.

此外,还可以使用 SQL ServerSQL Server 生成脚本向导来生成 Transact-SQLTransact-SQL 脚本。In addition, you can use the SQL ServerSQL Server Generate Script Wizard to generate Transact-SQLTransact-SQL scripts. 可以在 SQL Server Management StudioSQL Server Management Studio 对象资源管理器中右键单击对象,然后选择“生成脚本”菜单项。You can right-click objects in the SQL Server Management StudioSQL Server Management Studio Object Explorer, then select the Generate Script menu item. “生成脚本” 会启动向导,指导您完成创建脚本的过程。Generate Script launches the wizard, which guides you through the process of creating a script.

数据库引擎脚本任务Database Engine Scripting Tasks

任务说明Task Description 主题Topic
介绍如何使用 Management StudioManagement Studio 中的代码和文本编辑器来以交互方式开发、调试和运行 Transact-SQLTransact-SQL 脚本Describes how to use the code and text editors in Management StudioManagement Studio to interactively develop, debug, and run Transact-SQLTransact-SQL scripts 查询和文本编辑器 (SQL Server Management Studio)Query and Text Editors (SQL Server Management Studio)
介绍如何使用 实用工具一起运行的 实用工具从命令提示符运行 Transact-SQLTransact-SQL 脚本,包括以交互方式开发脚本的能力。Describes how to use the sqlcmd utility to run Transact-SQLTransact-SQL scripts from the command prompt, including the ability to interactively develop scripts. sqlcmd 操作指南主题sqlcmd How-to Topics
介绍如何将 SQL Server 组件集成到 Windows PowerShell 环境中,然后生成 PowerShell 脚本以便管理 SQL Server 实例和对象。Describes how to integrate the SQL Server components into a Windows PowerShell environment and then build PowerShell scripts for managing SQL Server instances and objects. SQL Server PowerShellSQL Server PowerShell
介绍如何使用 “生成和发布脚本” 向导创建从数据库重新创建一个或多个对象的 Transact-SQLTransact-SQL 脚本。Describes how to use the Generate and Publish Scripts wizard to create Transact-SQLTransact-SQL scripts that recreate one or more of the objects from a database. 生成脚本 (SQL Server Management Studio)Generate Scripts (SQL Server Management Studio)

另请参阅See Also

sqlcmd 实用工具 sqlcmd Utility
教程:编写 Transact-SQL 语句Tutorial: Writing Transact-SQL Statements