sp_data_source_objects (Transact-SQL)

  • Artículo

Se aplica a: SQL Server 2019 (15.x)

Devuelve la lista de objetos de tabla que están disponibles para virtualizarse.

Sintaxis

Convenciones de sintaxis 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'

Nombre del origen de datos externo del que se van a obtener los metadatos. @data_source es sysname.

[ @object_root_name = ] 'object_root_name'

Este parámetro es la raíz del nombre de los objetos que se van a buscar. @object_root_name es nvarchar(max), con un valor predeterminado de NULL.

Esta llamada solo devuelve objetos externos que comienzan con el valor establecido para @object_root_name.

Si un origen de datos ODBC se conecta a un sistema de administración de bases de datos relacionales (RDBMS) que usa nombres de tres partes, @object_root_name no puede contener un nombre de base de datos parcial. En estos casos, el parámetro @object_root_name debe contener las tres partes, siendo la tercera parte el nombre del objeto que se va a buscar.

Precaución

Debido a las diferencias entre las plataformas de datos externos, algunas no devuelven ningún resultado si se proporciona el valor predeterminado de NULL. Algunos tratan NULL como la falta de un filtro. Por ejemplo, RDMBS de Oracle no devolverá resultados si NULL se proporciona para @object_root_name.

[ @max_search_depth = ] max_search_depth

Este valor especifica la profundidad máxima (en partes) pasada la @object_root_name que deseamos buscar. @max_search_depth es un valor int con un valor predeterminado de 1.

Por ejemplo, un @max_search_depth de 1, con un @object_root_name que es el nombre de una base de datos de SQL Server, devolvería un esquema contenido dentro de la base de datos.

Un @max_search_depth de NULL devuelve información sobre @object_root_name si existe y no está vacía, en el caso del catálogo o el esquema.

[ @search_options = ] 'search_options'

El parámetro search_options es nvarchar(max) con un valor predeterminado de NULL.

Este parámetro no se usa, pero puede implementarse en el futuro.

Conjunto de resultados

Nombre de la columna Tipo de datos Descripción
OBJECT_TYPE nvarchar(200) Tipo del objeto (ejemplo: TABLE o DATABASE).
OBJECT_NAME nvarchar(max) Nombre completo del objeto. Se ha evadido con el carácter de comilla específico del back-end.
OBJECT_LEAF_NAME nvarchar(max) Nombre de objeto no calificado.
TABLE_LOCATION nvarchar(max) Cadena de ubicación de tabla válida que se podría usar para la instrucción CREATE EXTERNAL TABLE. Es NULL si no es aplicable.

Permisos

Requiere el permiso ALTER ANY EXTERNAL DATA SOURCE.

Comentarios

Debe instalarse la característica PolyBase en la instancia de SQL Server. Este procedimiento se introdujo por primera vez en SQL Server 2019 CU5.

Este procedimiento almacenado admite conectores para:

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

El procedimiento almacenado no admite el origen de datos ODBC genérico ni los conectores de Hadoop.

La noción de vacío frente a no vacío se relaciona con el comportamiento del controlador ODBC y la función SQLTables. No vacío indica que un objeto contiene tablas, no filas. Por ejemplo, un esquema vacío no contiene tablas en SQL Server. Una base de datos vacía no contiene tablas dentro de Teradata.

Los tipos de objeto están determinados por el controlador ODBC del origen de datos externo. Cada origen de datos externo determina qué califica como una tabla. Esto puede incluir objetos de base de datos como funciones en Teradata o sinónimos de Oracle. PolyBase no puede conectarse a algunos objetos ODBC como tablas externas y, por tanto, no tendrá un valor en la columna TABLE_LOCATION. A pesar de la ausencia de valores en TABLE_LOCATION, la presencia de uno de estos objetos ODBC puede hacer que una base de datos o un esquema no estén vacíos.

Use sp_data_source_objects y sp_data_source_table_columns para detectar objetos externos. Estos procedimientos almacenados del sistema devuelven el esquema de las tablas que están disponibles para virtualizarse. Azure Data Studio usa estos dos procedimientos almacenados para admitir la virtualización de datos. Use sp_data_source_table_columns para detectar esquemas de tablas externas representados en tipos de datos de SQL Server.

Tablas externas a colecciones de MongoDB que contienen matrices

Para crear tablas externas en colecciones de MongoDB que contienen matrices, debe usar la extensión de virtualización de datos para Azure Data Studio a fin de generar una instrucción CREATE EXTERNAL TABLE basada en el esquema que detecta el controlador ODBC de PolyBase para MongoDB. El controlador realiza automáticamente las acciones de acoplamiento. También puede usar sp_data_source_objects (Transact-SQL) para detectar el esquema de colección (columnas) y crear manualmente la tabla externa. El procedimiento almacenado sp_data_source_table_columns también realiza automáticamente el acoplamiento mediante el controlador ODBC de PolyBase para MongoDB. La extensión de virtualización de datos para Azure Data Studio y sp_data_source_table_columns usa los mismos procedimientos almacenados internos a fin de consultar el esquema externo.

Comentarios específicos del tipo de origen de datos

  • Teradata

    Las vistas del sistema de Teradata no usan la seguridad de nivel de fila (RLS), por lo que los usuarios pueden ver la existencia de tablas que no pueden consultar.

  • MongoDB

    Algunas versiones anteriores de MongoDB restringen la capacidad de enumerar todas las bases de datos a usuarios de tipo administrador. Los usuarios sin este permiso pueden obtener errores de autenticación al intentar ejecutar este procedimiento con un @object_root_name null.

  • Oracle

    Los sinónimos de Oracle no se admiten para su uso con PolyBase.

Ejemplos

SQL Server

En el ejemplo siguiente se devuelven todas las bases de datos, esquemas y vistas de tablas.

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]

En el ejemplo siguiente se devuelven todas las bases de datos.

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

En el ejemplo siguiente se devuelven todos los esquemas de una base de datos.

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

En el ejemplo siguiente se devuelven todas las tablas del 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

En el ejemplo siguiente se devuelven las tablas y esquemas completos, funciones, vistas, 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

En el ejemplo siguiente se devuelven todas las bases de datos y tablas, funciones, vistas, 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

En el ejemplo siguiente se devuelven todas las bases de datos y tablas.

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]

Contenido relacionado