sp_data_source_objects (Transact-SQL)

  • 项目

适用于： SQL Server 2019 (15.x)

返回可用于虚拟化的表对象的列表。

语法

Transact-SQL 语法约定

sp_data_source_objects
        [ @data_source = ] 'data_source'
    [ , [ @object_root_name = ] 'object_root_name' ]
    [ , [ @max_search_depth = ] max_search_depth ]
    [ , [ @search_options = ] 'search_options' ]
[ ; ]

参数

[ @data_source = ] 'data_source'

要从中获取元数据的外部数据源的名称。 @data_source 为 sysname。

[ @object_root_name = ] 'object_root_name'

此参数是要搜索的对象名称的根目录。 @object_root_name 是 nvarchar(max)，默认值为 NULL。

此调用仅返回以为 @object_root_name 设置的值开头的外部对象。

如果 ODBC 数据源连接到使用三部分名称 (@object_root_name) 的关系数据库管理系统 (RDBMS)，则 @object_root_name 不能包含部分数据库名称。 在这些情况下，参数 @object_root_name 应包含所有三个部分，第三部分是要搜索的对象名称。

注意

由于外部数据平台之间的差异，如果提供了默认值 NULL，某些平台不会返回任何结果。 有些将 NULL 视为缺少筛选器。 例如，如果为 @object_root_name 提供 NULL，则 Oracle RDMBS 不会返回结果。

[ @max_search_depth = ] max_search_depth

此值指定我们希望搜索的 @object_root_name 之后的最大深度（部分）。 @max_search_depth 为 int，默认值为 1。

例如，@max_search_depth 为 1，而 @object_root_name 是 SQL Server 数据库的名称，将返回数据库中包含的架构。

如果 @object_root_name 存在且非空（对于目录或架构），则 NULL 的 @max_search_depth 返回有关 @object_root_name 的信息。

[ @search_options = ] 'search_options'

search_options 参数为 nvarchar(max)，默认值为 NULL。

此参数未使用，但将来可能会实现。

结果集

列名称 数据类型 描述
OBJECT_TYPE nvarchar(200) 对象的类型（示例：TABLE 或 DATABASE）。
OBJECT_NAME nvarchar(max) 对象的完全限定名称。 使用特定于后端的引号字符进行转义。
OBJECT_LEAF_NAME nvarchar(max) 未限定的对象名称。
TABLE_LOCATION nvarchar(max) 可用于 CREATE EXTERNAL TABLE 语句的有效表位置字符串。 如果不适用则为 NULL。

权限

需要 ALTER ANY EXTERNAL DATA SOURCE 权限。

注解

SQL Server 实例必须安装 PolyBase 功能。 此过程首次在 SQL Server 2019 CU5 中引入。

此存储过程支持以下项的连接器：

  • SQL Server
  • Oracle
  • Teradata
  • MongoDB
  • Azure Cosmos DB

存储过程不支持通用 ODBC 数据源或 Hadoop 连接器。

空与非空的概念与 ODBC 驱动程序和 SQLTables 函数的行为相关。 非空表示对象包含表，而不是行。 例如，空架构不包含 SQL Server 中的表。 空数据库包含 Teradata 内没有的表。

对象类型由外部数据源的 ODBC 驱动程序确定。 每个外部数据源均确定表的资格。 这可以包括数据库对象，如 Teradata 中的函数或 Oracle 中的同义词。 PolyBase 无法作为外部表连接到某些 ODBC 对象，因此在 TABLE_LOCATION 列中没有值。 尽管 TABLE_LOCATION 中没有值，但存在其中一个 ODBC 对象可能会使数据库或架构不为空。

使用 sp_data_source_objects 和 sp_data_source_table_columns 发现外部对象。 这些系统存储过程会返回可供虚拟化的表的架构。 Azure Data Studio 使用这两个存储过程来支持数据虚拟化。 使用 sp_data_source_table_columns 发现 SQL Server 数据类型中表示的外部表架构。

包含数组的 MongoDB 集合的外部表

要创建包含数组的 MongoDB 集合的外部表，应使用 Azure Data Studio 的数据虚拟化扩展，根据 PolyBase ODBC Driver for MongoDB 检测到的架构生成 CREATE EXTERNAL TABLE 语句。 驱动程序会自动执行平展操作。 或者，可使用 sp_data_source_objects (Transact-SQL) 来检测集合架构（列）并手动创建外部表。 sp_data_source_table_columns 存储过程还通过 PolyBase ODBC Driver for MongoDB 驱动程序自动执行平展。 Azure Data Studio 的数据虚拟化扩展和 sp_data_source_table_columns 使用相同的内部存储过程来查询外部架构。

数据源类型特定备注

  • Teradata

    Teradata 系统视图不使用行级别安全性 (RLS)，因此用户可以看到其无法查询的表是否存在。

  • MongoDB

    某些早期版本的 MongoDB 限制向类似管理员的用户列出所有数据库的功能。 没有此权限的用户可能会收到尝试使用空的 @object_root_name 执行此过程的身份验证错误。

  • Oracle

    不支持将 Oracle 同义词用于 PolyBase。

示例

SQL Server

以下示例返回所有数据库、架构和表/视图

DECLARE @data_source SYSNAME = N'ExternalDataSourceName';
DECLARE @object_root_name NVARCHAR(MAX) = NULL;
DECLARE @max_search_depth INT = 3;

EXEC sp_data_source_objects @data_source,
    @object_root_name,
    @max_search_depth;
Object_Type OBJECT_NAME OBJECT_LEAF_NAME TABLE_LOCATION
DATABASE "database" database NULL
SCHEMA "database"."dbo" dbo NULL
TABLE "database"."dbo"."customer" customer [database].[dbo].[customer]
TABLE "database"."dbo"."item" item [database].[dbo].[item]
TABLE "database"."dbo"."nation" nation [database].[dbo].[nation]

以下示例返回所有数据库

DECLARE @data_source SYSNAME = N'ExternalDataSourceName';
DECLARE @object_root_name NVARCHAR(MAX) = NULL;

EXEC sp_data_source_objects @data_source, @object_root_name;
Object_Type OBJECT_NAME OBJECT_LEAF_NAME TABLE_LOCATION
DATABASE UserDatabase UserDatabase NULL
DATABASE master master NULL
DATABASE msdb msdb NULL
DATABASE tempdb tempdb NULL
DATABASE database database NULL

以下示例返回数据库中的所有架构

DECLARE @data_source SYSNAME = N'ExternalDataSourceName';
DECLARE @object_root_name NVARCHAR(MAX) = N'[database]';

EXEC sp_data_source_objects @data_source, @object_root_name;
Object_Type OBJECT_NAME OBJECT_LEAF_NAME TABLE_LOCATION
SCHEMA "database"."dbo" dbo NULL
SCHEMA "database"."INFORMATION_SCHEMA" INFORMATION_SCHEMA NULL
SCHEMA "database"."sys" sys NULL

以下示例返回架构中的所有表

DECLARE @data_source SYSNAME = N'ExternalDataSourceName';
DECLARE @object_root_name NVARCHAR(MAX) = N'[database].[dbo]';

EXEC sp_data_source_objects @data_source, @object_root_name;
Object_Type OBJECT_NAME OBJECT_LEAF_NAME TABLE_LOCATION
TABLE "database"."dbo"."customer" customer [database].[dbo].[customer]
TABLE "database"."dbo"."item" item [database].[dbo].[item]
TABLE "database"."dbo"."nation" nation [database].[dbo].[nation]
TABLE "database"."dbo"."orders" orders [database].[dbo].[orders]
TABLE "database"."dbo"."part" part [database].[dbo].[part]

Oracle

以下示例返回完整的架构和表、函数、视图等。

DECLARE @data_source SYSNAME = N'ExternalDataSourceName';
DECLARE @object_root_name NVARCHAR(MAX) = N'[OracleObjectRoot]';
DECLARE @max_search_depth INT = 2;

EXEC sp_data_source_objects @data_source, @object_root_name, @max_search_depth;
Object_Type OBJECT_NAME OBJECT_LEAF_NAME TABLE_LOCATION
VIEW "SYS"."ALL_SQLSET_STATEMENTS" ALL_SQLSET_STATEMENTS [ORACLEOBJECTROOT].[SYS].[ALL_SQLSET_STATEMENTS]
SYSTEM TABLE "SYS"."BOOTSTRAP$" BOOTSTRAP$ [ORACLEOBJECTROOT].[SYS].[BOOTSTRAP$]
SYNONYM "PUBLIC"."ALL_ALL_TABLES" ALL_ALL_TABLES NULL
SCHEMA "database" database NULL
TABLE "database"."customer" customer [ORACLEOBJECTROOT].[database].[customer]

Teradata

以下示例返回所有数据库和表、函数、视图等。

DECLARE @data_source SYSNAME = N'ExternalDataSourceName';
DECLARE @object_root_name NVARCHAR(MAX) = NULL;
DECLARE @max_search_depth INT = 2;

EXEC sp_data_source_objects @data_source, @object_root_name, @max_search_depth;
Object_Type OBJECT_NAME OBJECT_LEAF_NAME TABLE_LOCATION
FUNCTION "SYSLIB"."ExtractRoles" ExtractRoles NULL
SYSTEM TABLE "DBC"."UDTCast" UDTCast [DBC].[UDTCast]
TYPE "SYSUDTLIB"."XML" XML NULL
DATABASE "database" database NULL
TABLE "database"."customer" customer [database].[customer]

Mongo DB

以下示例返回所有数据库和表。

DECLARE @data_source SYSNAME = N'ExternalDataSourceName';
DECLARE @object_root_name NVARCHAR(MAX) = NULL;
DECLARE @max_search_depth INT = 2;

EXEC sp_data_source_objects @data_source, @object_root_name, @max_search_depth;
Object_Type OBJECT_NAME OBJECT_LEAF_NAME TABLE_LOCATION
DATABASE "database" database NULL
TABLE "database"."customer" customer [database].[customer]
TABLE "database"."item" item [database].[item]
TABLE "database"."nation" nation [database].[nation]
TABLE "database"."orders" orders [database].[orders]

相关内容