sp_data_source_objects (Transact-SQL)
適用対象: 
SQL Server 2019 (15.x)
仮想化可能なテーブル オブジェクトのリストを返します。
構文
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 データ ソースが 3 部構成の名前を使用するリレーショナル データベース管理システム (RDBMS) に接続する場合、 @object_root_name データベース名の一部を含めることはできません。 このような場合、パラメーター @object_root_name には 3 つの部分をすべて含める必要があります。3 番目の部分は検索するオブジェクト名です。
注意事項
外部データ プラットフォーム間の違いにより、一部のプラットフォームでは、既定値 NULL が指定されている場合は結果が返されません。 フィルターの欠如として NULL を扱うものもあります。 たとえば、NULL が @object_root_name に対して指定されている場合、Oracle RDMBS は結果を返しません。
[ @max_search_depth = ] max_search_depth
この値は、検索する @object_root_name を超える最大深度 (部分単位) を指定します。 @max_search_depth は既定値 1 の int です。
たとえば、 SQL Server データベースの名前である@object_root_name を持つ 1 の @max_search_depth は、データベース内に含まれる schemata を返します。
@max_search_depthは、カタログまたはスキーマの NULL 場合、@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 では、これら 2 つのストアド プロシージャを使用してデータ仮想化をサポートします。 sp_data_source_table_columns を使用して、SQL Server データ型で表される外部テーブル スキーマを検出します。
配列を含む MongoDB コレクションの外部テーブル
配列を含む MongoDB コレクションに外部テーブルを作成するには、Azure Data Studio 用のデータ仮想化の拡張機能を使用して、MongoDB 用 PolyBase ODBC ドライバーによって検出されたスキーマに基づいて CREATE EXTERNAL TABLE ステートメントを生成する必要があります。 フラット化アクションは、ドライバーによって自動的に実行されます。 または、sp_data_source_objects を使用してコレクション スキーマ (列) を検出し、外部テーブルを手動で作成することもできます。 sp_data_source_table_columns ストアド プロシージャでは、MongoDB 用 PolyBase ODBC ドライバーによってフラット化も自動的に実行されます。 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 |
次の例は、データベースのすべてのスキーマを返します
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] |