SQL Server PowerShell 提供程序SQL Server PowerShell Provider

适用于: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

用于 Windows PowerShell 的 SQL ServerSQL Server 提供程序用类似于文件系统路径的路径公开 SQL ServerSQL Server 对象的层次结构。The SQL ServerSQL Server provider for Windows PowerShell exposes the hierarchy of SQL ServerSQL Server objects in paths similar to file system paths. 可以使用路径来查找对象,然后使用 SQL ServerSQL Server 管理对象 (SMO) 模型中的方法来针对对象执行操作。You can use the paths to locate an object, and then use methods from the SQL ServerSQL Server Management Object (SMO) models to perform actions on the objects.


SQL Server PowerShell 模块有两种;SqlServer 和 SQLPS 。There are two SQL Server PowerShell modules; SqlServer and SQLPS.

SqlServer 模块是当前要使用的 PowerShell 模块。The SqlServer module is the current PowerShell module to use.

虽然 SQL Server 安装附带了 SQLPS 模块(用于实现后向兼容性),但该模块不再更新。The SQLPS module is included with the SQL Server installation (for backward compatibility) but is no longer updated.

SqlServer 模块不仅包含 SQLPS 更新版本的 cmdlet,还包含新的 cmdlet 以支持最新的 SQL 功能 。The SqlServer module contains updated versions of the cmdlets in SQLPS and includes new cmdlets to support the latest SQL features.

PowerShell 库安装 SqlServer 模块。Install the SqlServer module from the PowerShell Gallery.

有关详细信息,请参阅 SQL Server PowerShellFor more information, see SQL Server PowerShell.

SQL Server PowerShell 提供程序的优点Benefits of the SQL Server PowerShell Provider

通过 SQL ServerSQL Server 提供程序实现的路径有助于轻松地以交互方式查看 SQL Server 实例中的所有对象。The paths implemented by the SQL ServerSQL Server provider enable easily and interactively reviewing all of the objects in an instance of SQL Server. 您可以使用与您通常用于导航文件系统路径的命令相似的 Windows PowerShell 别名来导航路径。You can navigate the paths using Windows PowerShell aliases similar to the commands you typically use to navigate file system paths.

SQL Server PowerShell 层次结构The SQL Server PowerShell Hierarchy

可以用层次结构表示其数据或对象模型的产品使用 Windows PowerShell 提供程序来公开层次结构。Products whose data or object models can be represented in a hierarchy use Windows PowerShell providers to expose the hierarchies. 该层次结构是使用与 Windows 文件系统所用结构相似的驱动器和路径结构公开的。The hierarchy is exposed by using a drive and path structure similar to what the Windows file system uses.

每个 Windows PowerShell 提供程序都实现一个或多个驱动器。Each Windows PowerShell provider implements one or more drives. 每个驱动器都是相关对象的层次结构的根节点。Each drive is the root node of a hierarchy of related objects. SQL ServerSQL Server 提供程序实现一个 SQLSERVER: 驱动器。The SQL ServerSQL Server provider implements a SQLSERVER: drive. 该提供程序还为 SQLSERVER: 驱动器定义了一组主文件夹。The provider also defines a set of primary folders for the SQLSERVER: drive. 每个文件夹及其子文件夹表示一组可通过使用 SQL ServerSQL Server 管理对象模型访问的对象。Each folder and its subfolders represent the set of objects that can be accessed by using a SQL ServerSQL Server management object model. 当关注某个以这些主文件夹之一开始的路径中的子文件夹时,可以使用相关对象模型中的方法对该节点所表示的对象执行操作。When you are focused on a subfolder in a path that starts with one of these primary folders, you can use the methods from the associated object model to perform actions on the object that is represented by the node. 下表列出了由 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 提供程序实现的 Windows PowerShell 文件夹:The Windows PowerShell folders implemented by the SQL Server 2019 (15.x)SQL Server 2019 (15.x) provider are listed in the following table:

FolderFolder SQL Server 对象模型命名空间SQL Server object model namespace 对象Objects
SQLSERVER:\SQL Microsoft.SqlServer.Management.Smo



数据库对象,如表、视图和存储过程。Database objects, such as tables, views, and stored procedures.
SQLSERVER:\SQLPolicy Microsoft.SqlServer.Management.Dmf

基于策略的管理对象,如策略和方面。Policy-based management objects, such as policies and facets.
SQLSERVER:\SQLRegistration Microsoft.SqlServer.Management.RegisteredServers

已注册的服务器对象,如服务器组和已注册服务器。Registered server objects, such as server groups and registered servers.
SQLSERVER:\Utility Microsoft.SqlServer.Management.Utility 实用工具对象,例如, 数据库引擎Database Engine的托管实例Utility objects, such as managed instances of the 数据库引擎Database Engine.
SQLSERVER:\DAC Microsoft.SqlServer.Management.DacMicrosoft.SqlServer.Management.Dac 数据层应用程序对象(如 DAC 包)和操作(如部署 DAC)。Data-tier application objects such as DAC packages, and operations such as deploying a DAC.
SQLSERVER:\DataCollection Microsoft.SqlServer.Management.Collector 数据收集器对象,如收集组和配置存储区。Data collector objects, such as collection sets and configuration stores.
SQLSERVER:\SSIS Microsoft.SqlServer.Management.IntegrationServices Integration ServicesIntegration Services 对象,如项目、包和环境。objects such as projects, packages, and environments.
SQLSERVER:\XEvent Microsoft.SqlServer.Management.XEvent SQL Server 扩展事件SQL Server Extended Events
SQLSERVER:\DatabaseXEvent Microsoft.SqlServer.Management.XEventDbScopedMicrosoft.SqlServer.Management.XEventDbScoped SQL Server 扩展事件SQL Server Extended Events
SQLSERVER:\SQLAS Microsoft.AnalysisServices Analysis ServicesAnalysis Services 对象,例如多维数据集、聚合和维度。objects such as cubes, aggregations, and dimensions.

例如,可以使用 SQLSERVER:\SQL 文件夹作为路径的开头,该路径可以表示 SMO 对象模型支持的任何对象。For example, you can use the SQLSERVER:\SQL folder to start paths that can represent any object that is supported by the SMO object model. SQLSERVER:\SQL 路径的前导部分是 SQLSERVER:\SQL\计算机名称\实例名称The leading part of a SQLSERVER:\SQL path is SQLSERVER:\SQL\ComputerName\InstanceName. 实例名称后面的节点在对象集合(如 数据库视图)和对象名称(如 AdventureWorks2012)之间交替变化。The nodes after the instance name alternate between object collections (such as Databases or Views) and object names (such as AdventureWorks2012). 架构不用对象类表示。Schemas are not represented as object classes. 在为架构中的顶层对象(如表或视图)指定节点时,必须以 SchemaName.ObjectName格式指定对象名称。When you specify the node for a top-level object in a schema, such as a table or view, you must specify the object name in the format SchemaName.ObjectName.

以下示例显示 AdventureWorks2012 数据库的 Purchasing 架构中的 Vendor 表的路径,该数据库位于本地计算机上的 数据库引擎Database Engine 默认实例中:The following example shows the path of the Vendor table in the Purchasing schema of the AdventureWorks2012 database in a default instance of the 数据库引擎Database Engine on the local computer:


有关 SMO 对象模型层次结构的详细信息,请参阅 SMO Object Model DiagramFor more information about the SMO object model hierarchy, see SMO Object Model Diagram.

路径中的集合节点与相关对象模型中的集合类相关联。Collection nodes in a path are associated with a collection class in the associated object model. 对象名节点与相关对象模型中的对象类相关联,如下表中所示:Object name nodes are associated with an object class in the associated object model, as in the following table:

路径Path SMO 类SMO class
SQLSERVER:\SQL\MyComputer\DEFAULT\Databases DatabaseCollection
SQLSERVER:\SQL\MyComputer\DEFAULT\Databases\AdventureWorks2012 Database

SQL Server 提供程序任务SQL Server Provider Tasks

任务说明Task Description 项目Article
介绍如何使用 Windows PowerShell cmdlet 导航路径中的节点,以及如何在每个节点获取该节点上对象的列表。Describes how to use Windows PowerShell cmdlets to navigate through the nodes in a path, and at each node get a list of the objects at that node. 导航 SQL Server PowerShell 路径Navigate SQL Server PowerShell Paths
介绍如何使用 SMO 方法和属性对路径中的节点表示的对象进行报告和执行任务。Describes how to use the SMO methods and properties to report on and perform work on the object represented by a node in a path. 还介绍如何获取该节点的 SMO 方法和属性的列表。Also describes how to get a list of the SMO methods and properties for that node. 使用 SQL Server PowerShell 路径Work With SQL Server PowerShell Paths
介绍如何将 SMO 统一资源名称 (URN) 转换为 SQL Server 提供程序路径。Describes how to convert a SMO Uniform Resource Name (URN) to a SQL Server provider path. 将 URN 转换为 SQL Server 提供程序路径Convert URNs to SQL Server Provider Paths
介绍如何使用 SQL ServerSQL Server 提供程序打开 SQL Server 身份验证连接。Describes how to open SQL Server Authentication connections by using the SQL ServerSQL Server provider. 默认情况下,提供程序使用通过运行 Windows PowerShell 会话的 Windows 帐户的凭据生成的 Windows 身份验证连接。By default, the provider uses Windows Authentication connections made using the credentials of the Windows account running the Windows PowerShell session. 在数据库引擎 PowerShell 中管理身份验证Manage Authentication in Database Engine PowerShell

后续步骤Next steps

SQL Server PowerShellSQL Server PowerShell