sp_data_source_objects (Transact-SQL)

  • Artigo

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

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

Sintaxe

Convenções de sintaxe de 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' ]
[ ; ]

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çãoSQLTables. 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 (Transact-SQL) 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]

Conteúdo relacionado