sp_data_source_objects (Transact-SQL)

  • 發行項

適用於： SQL Server 2019 (15.x)

傳回可供虛擬化的資料表物件清單。

Syntax

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 資料來源連線到使用三部分名稱的關聯式資料庫管理系統 (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 資料庫的名稱，會傳回資料庫中所包含的 schemata。

在目錄或結構描述的情況下，@max_search_depth 為 NULL 時，會傳回關於 @object_root_name 是否存在且非空白的資訊。

[ @search_options = ] 'search_options'

search_options 參數是預設值為 NULL 的 Nvarchar(MAX)。

此參數未使用，但未來可能實作。

結果集

資料行名稱 資料類型 描述
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 會限制將所有資料庫列出給類似系統管理員的使用者。 沒有此授權的使用者可能會收到嘗試以 Null @object_root_name 執行此程序的驗證錯誤。

  • Oracle

    搭配使用 PolyBase 時，不支援 Oracle 同義字。

範例

SQL Server

下列範例會傳回所有資料庫、schemata 和資料表/檢視

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

下列範例會傳回資料庫中的所有 schemata

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

下列範例會傳回完整的 schemata 和資料表、函式、檢視等等。

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]

相關內容