sp_data_source_objects (Transact-SQL)

  • Articolo

Si applica a: SQL Server 2019 (15.x)

restituisce un elenco di oggetti tabella disponibili per la virtualizzazione.

Sintassi

Convenzioni relative alla sintassi 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' ]
[ ; ]

Argomenti

[ @data_source = ] 'data_source'

Nome dell'origine dati esterna da cui ottenere i metadati. @data_source è sysname.

[ @object_root_name = ] 'object_root_name'

Questo parametro è la radice del nome degli oggetti da cercare. @object_root_name è nvarchar(max), con il impostazione predefinita NULL.

Questa chiamata restituisce solo oggetti esterni che iniziano con il valore impostato per @object_root_name.

Se un'origine dati ODBC si connette a un sistema di gestione di database relazionale (RDBMS) che usa nomi in tre parti, @object_root_name non può contenere un nome di database parziale. In questi casi, il parametro @object_root_name deve contenere tutte e tre le parti, con la terza parte che rappresenta il nome dell'oggetto da cercare.

Attenzione

A causa delle differenze tra piattaforme dati esterne, alcune piattaforme non restituiscono risultati se viene fornito il valore predefinito di NULL. Alcuni considerano NULL la mancanza di un filtro. Ad esempio, Oracle RDMBS non restituirà i risultati se NULL viene fornito per @object_root_name.

[ @max_search_depth = ] max_search_depth

Questo valore specifica la profondità massima (in parti) oltre il @object_root_name che si desidera cercare. @max_search_depth è un valore int con impostazione predefinita 1.

Ad esempio, un @max_search_depth di 1, con un @object_root_name che corrisponde al nome di un database di SQL Server, restituirà schemata contenuto all'interno del database.

Un @max_search_depth di NULL restituisce informazioni su @object_root_name se è esistente e non è vuoto, nel caso di catalogo o schema.

[ @search_options = ] 'search_options'

Il parametro search_options è nvarchar(max) con una impostazione predefinita di NULL.

Questo parametro non viene usato ma può essere implementato in futuro.

Set di risultati

Nome colonna Tipo di dati Descrizione
OBJECT_TYPE nvarchar(200) Tipo dell'oggetto (esempio: TABLE o DATABASE).
OBJECT_NAME nvarchar(max) Nome completo dell'oggetto . Carattere di escape con virgolette specifiche del back-end.
OBJECT_LEAF_NAME nvarchar(max) Nome dell'oggetto non qualificato.
TABLE_LOCATION nvarchar(max) Una stringa di posizione della tabella valida che può essere utilizzata per l'istruzione CREARE TABELLA ESTERNA. È NULL se non è applicabile.

Autorizzazioni

È necessaria l'autorizzazione ALTER ANY EXTERNAL DATA SOURCE.

Osservazioni:

L'istanza di SQL Server deve avere la funzionalità PolyBase installata. Questa procedura è stata introdotta per la prima volta in SQL Server 2019 CU5.

Questa stored procedure supporta i connettori per:

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

La stored procedure non supporta connettori di origine dati ODBC o hadoop.

Il concetto di vuoto e non vuoto è correlato al comportamento del driver ODBC e della SQLTables funzione. Non vuoto indica che un oggetto contiene tabelle, non righe. Ad esempio, uno schema vuoto non contiene tabelle in SQL Server. Un database vuoto non contiene tabelle all'interno di Teradata.

I tipi di oggetto sono determinati dal driver ODBC dell'origine dati esterna. Ogni origine dati esterna determina cosa qualifica come tabella. Può includere oggetti database come funzioni in Teradata o sinonimi in Oracle. PolyBase non è in grado di connettersi ad alcuni oggetti ODBC come tabelle esterne e pertanto non avrà un valore nella colonna TABLE_LOCATION. Nonostante l'assenza di valori in TABLE_LOCATION, la presenza di uno di questi oggetti ODBC può rendere un database o uno schema non vuoto.

Usare sp_data_source_objects e sp_data_source_table_columns per individuare oggetti esterni. Queste stored procedure di sistema restituiscono lo schema delle tabelle disponibili per la virtualizzazione. Azure Data Studio usa queste due stored procedure per supportare la virtualizzazione dei dati. Usare sp_data_source_table_columns per individuare gli schemi di tabella esterni rappresentati nei tipi di dati di SQL Server.

Tabelle esterne per raccolte MongoDB che contengono matrici

Per creare tabelle esterne nelle raccolte MongoDB contenenti matrici, è necessario usare l'estensione di virtualizzazione dei dati per Azure Data Studio per produrre un'istruzione CREATE EXTERNAL TABLE basata sullo schema rilevato dal driver ODBC PolyBase per MongoDB. Le prestazioni di appiattimento vengono eseguite automaticamente dal driver. In alternativa, è possibile usare sp_data_source_objects (Transact-SQL) per rilevare lo schema della raccolta (colonne) e creare manualmente la tabella esterna. La stored procedure sp_data_source_table_columns esegue inoltre l'appiattimento automatico tramite il driver ODBC di PolyBase per il driver MongoDB. L'estensione di virtualizzazione dei dati per Azure Data Studio e sp_data_source_table_columns usano le stesse stored procedure interne per eseguire query sullo schema esterno.

Osservazioni specifiche relative al tipo di origine dati

  • Teradata

    Le visualizzazioni di sistema Teradata non usano la sicurezza a livello di riga e quindi gli utenti possono visualizzare l'esistenza di tabelle che non possono eseguire query.

  • MongoDB

    Alcune versioni precedenti di MongoDB applicano restrizioni alla possibilità di elencare tutti i database agli utenti nel ruolo di amministratori. Gli utenti senza questa autorizzazione potrebbero ricevere errori di autenticazione durante il tentativo di eseguire questa procedura con un Null @object_root_name.

  • Oracle

    I sinonimi di Oracle non possono essere usati con PolyBase.

Esempi

SQL Server

Nell'esempio seguente vengono restituiti tutti i database, schemata e tabelle/visualizzazioni

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]

Nell'esempio seguente vengono restituiti tutti i database.

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

Nell'esempio seguente vengono restituiti tutti gli schemi in un database.

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

Nell'esempio seguente vengono restituite tutte le tabelle nello schema.

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

Nell'esempio seguente vengono restituiti lo schema completo e le tabelle, le funzioni, le visualizzazioni e così via.

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

Nell'esempio seguente vengono restituiti tutti i database, le funzioni, visualizzazioni, ecc.

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]

MongoDB

Nell'esempio seguente vengono restituiti tutti i database e le tabelle.

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]

Contenuto correlato