sp_data_source_objects (Transact-SQL)

Artigo
08/21/2024

Aplica-se a: SQL Server 2019 (15.x)

Retorna a lista de objetos de tabela que estão disponíveis para serem virtualizados.

Sintaxe

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

Argumentos

[ @data_source = ] 'data_source'

O nome da fonte de dados externa da qual se obtêm os metadados. @data_source é sysname.

[ @object_root_name = ] 'object_root_name'

Esse parâmetro é a raiz do nome do(s) objeto(s) a ser(em) pesquisado(s). @object_root_name é nvarchar(max), com um padrão de NULL.

Essa chamada retorna apenas objetos externos que começam com o valor definido para @object_root_name.

Se uma fonte de dados ODBC se conectar a um sistema de gerenciamento de banco de dados relacional (RDBMS) que usa nomes de três partes, @object_root_name não poderá conter um nome de banco de dados parcial. Nesses casos, o parâmetro @object_root_name deve conter as três partes, sendo que a terceira parte é o nome do objeto a ser pesquisado.

Cuidado

Devido às diferenças entre as plataformas de dados externos, algumas plataformas não retornarão resultados se o valor padrão de NULL for fornecido. Alguns tratam NULL como a ausência de um filtro. Por exemplo, o Oracle RDMBS não retornará resultados se NULL for fornecido para @object_root_name.

[ @max_search_depth = ] max_search_depth

Esse valor especifica a profundidade máxima (em partes) após @object_root_name que desejamos pesquisar. @max_search_depth é um int com um padrão de 1.

Por exemplo, um @max_search_depth de 1, com um @object_root_name que é o nome de um banco de dados do SQL Server, retornaria um esquema contido no banco de dados.

Um @max_search_depth de NULL retornará informações sobre @object_root_name se ele existir e não estiver vazio, no caso de catálogo ou esquema.

[ @search_options = ] 'search_options'

O parâmetro search_options é nvarchar(max) com um padrão de NULL.

Esse parâmetro não é usado, mas pode ser implementado no futuro.

Conjunto de resultados

Nome da coluna	Tipo de dados	Descrição
`OBJECT_TYPE`	nvarchar(200)	O tipo do objeto (exemplo: TABLE ou DATABASE).
`OBJECT_NAME`	nvarchar(max)	O nome totalmente qualificado do objeto. Escapado usando o caractere de aspas específico do back-end.
`OBJECT_LEAF_NAME`	nvarchar(max)	O nome do objeto não qualificado.
`TABLE_LOCATION`	nvarchar(max)	Uma cadeia de caracteres de localização de tabela válida que pode ser usada para o comando CREATE EXTERNAL TABLE. Será `NULL` se não for aplicável.

Permissões

Requer a permissão ALTER ANY EXTERNAL DATA SOURCE.

Comentários

A instância do SQL Server deve ter o recurso PolyBase instalado. Esse procedimento foi introduzido pela primeira vez no SQL Server 2019 CU5.

Esse procedimento armazenado oferece suporte a conectores para:

SQL Server
Oracle
Teradata
MongoDB
Azure Cosmos DB

O procedimento armazenado não é compatível com fontes de dados ODBC genéricas ou conectores Hadoop.

A noção de vazio x não vazio está relacionada ao comportamento do driver ODBC e da função SQLTables. Não vazio indica que um objeto contém tabelas, e não linhas. Por exemplo, um esquema vazio não contém tabelas no SQL Server. Um banco de dados vazio não contém tabelas dentro do Teradata.

Os tipos de objetos são determinados pelo driver ODBC da fonte de dados externa. Cada fonte de dados externa determina o que se qualifica como uma tabela. Isso pode incluir objetos de banco de dados como funções no Teradata ou sinônimos no Oracle. O PolyBase não pode se conectar a alguns objetos ODBC como tabelas externas e, portanto, não terá um valor na coluna TABLE_LOCATION. Apesar da ausência de valores em TABLE_LOCATION, a presença de um desses objetos ODBC pode tornar um banco de dados ou esquema não vazio.

Use sp_data_source_objects e sp_data_source_table_columns para descobrir objetos externos. Esses procedimentos armazenados no sistema retornam o esquema das tabelas que estão disponíveis para serem virtualizadas. O Azure Data Studio usa esses dois procedimentos armazenados para dar suporte à virtualização de dados. Use sp_data_source_table_columns para descobrir esquemas de tabelas externas representadas em tipos de dados do SQL Server.

Tabelas externas para coleções do MongoDB que contêm matrizes

Para criar tabelas externas para coleções do MongoDB que contenham matrizes, você deve usar a extensão de virtualização de dados do Azure Data Studio para produzir uma instrução CREATE EXTERNAL TABLE, com base no esquema detectado pelo driver ODBC PolyBase para MongoDB. As ações de nivelamento são executadas automaticamente pelo driver. Como alternativa, você pode usar sp_data_source_objects para detectar o esquema de coleção (colunas) e criar manualmente a tabela externa. O procedimento armazenado sp_data_source_table_columns também executa automaticamente o nivelamento por meio do driver ODBC do PolyBase para o driver MongoDB. A extensão de Virtualização de Dados para o Azure Data Studio e o sp_data_source_table_columns usam os mesmos procedimentos armazenados internos para consultar o esquema externo.

Observações específicas do tipo de fonte de dados

Teradata

As visualizações do sistema Teradata não usam segurança em nível de linha (RLS) e, portanto, os usuários podem ver a existência de tabelas que não podem consultar.
MongoDB

Algumas versões anteriores do MongoDB restringem a capacidade de listar todos os bancos de dados a usuários do tipo administrador. Os usuários sem essa permissão podem receber erros de autenticação ao tentar executar esse procedimento com um @object_root_namenulo.
Oracle

Os sinônimos Oracle não são suportados para uso com o PolyBase.

Exemplos

SQL Server

O exemplo a seguir retorna todos os bancos de dados, esquemas e tabelas/exibições

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]`

O exemplo a seguir retorna todos os bancos de dados

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`

O exemplo a seguir retorna todos os esquemas em um banco de dados

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`

O exemplo a seguir retorna todas as tabelas do esquema

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

O exemplo a seguir retorna o esquema completo e as tabelas, funções, exibições etc.

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

O exemplo a seguir retorna todos os bancos de dados e tabelas, funções, exibições etc.

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

O exemplo a seguir retorna todos os bancos de dados e tabelas.

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]`

Partilhar via

sp_data_source_objects (Transact-SQL)

Sintaxe

Argumentos

[ @data_source = ] 'data_source'

[ @object_root_name = ] 'object_root_name'

[ @max_search_depth = ] max_search_depth

[ @search_options = ] 'search_options'

Conjunto de resultados

Permissões

Comentários

Tabelas externas para coleções do MongoDB que contêm matrizes

Observações específicas do tipo de fonte de dados

Exemplos

SQL Server

Oracle

Teradata

Mongo DB

Comentários

Recursos adicionais

Partilhar via

sp_data_source_objects (Transact-SQL)

Sintaxe

Argumentos

[ @data_source = ] 'data_source'

[ @object_root_name = ] 'object_root_name'

[ @max_search_depth = ] max_search_depth

[ @search_options = ] 'search_options'

Conjunto de resultados

Permissões

Comentários

Tabelas externas para coleções do MongoDB que contêm matrizes

Observações específicas do tipo de fonte de dados

Exemplos

SQL Server

Oracle

Teradata

Mongo DB

Conteúdo relacionado

Comentários

Recursos adicionais