sp_data_source_objects (Transact-SQL)

  • Статья

Область применения: SQL Server 2019 (15.x)

Возвращает список объектов таблиц, доступных для виртуализации.

Синтаксис

Соглашения о синтаксисе 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' ]
[ ; ]

Аргументы

[ @data_source = ] 'data_source'

Имя внешнего источника данных для получения метаданных. @data_source .sysname

[ @object_root_name = ] 'object_root_name'

Этот параметр является корнем имени объектов для поиска. @object_root_name имеет значение nvarchar(max)по умолчаниюNULL.

Этот вызов возвращает только внешние объекты, начинающиеся с набора значений для @object_root_name.

Если источник данных ODBC подключается к реляционной системе управления базами данных (RDBMS), которая использует три части, @object_root_name не может содержать частичное имя базы данных. В этих случаях параметр @object_root_name должен содержать все три части, а третья часть — имя объекта для поиска.

Внимание

Из-за различий между внешними платформами данных некоторые платформы не возвращают результаты, если указано значение NULL по умолчанию. Некоторые рассматриваются NULL как отсутствие фильтра. Например, Oracle RD МБ S не возвращает результаты, если NULL они предоставляются для @object_root_name.

[ @max_search_depth = ] max_search_depth

Это значение указывает максимальную глубину (в частях) мимо @object_root_name , которую мы хотим искать. @max_search_depth — это int с значением по умолчанию1.

Например, @max_search_depth 1 с @object_root_name , которая является именем базы данных SQL Server, вернет схему, содержащуюся в базе данных.

@max_search_depth NULL возвращает информацию о @object_root_name, если она существует и не пуста, в случае каталога или схемы.

[ @search_options = ] 'search_options'

Параметр search_options имеет значение nvarchar(max) с значением по умолчанию NULL.

Этот параметр не используется, но может быть реализован в будущем.

Результирующий набор

Имя столбца Тип данных Description
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 использует эти две хранимые процедуры для поддержки виртуализации данных. Используйте sp_data_source_table_columns для обнаружения схем внешней таблицы, представленных в типах данных SQL Server.

Внешние таблицы для коллекций MongoDB, содержащих массивы

Чтобы создать внешние таблицы для коллекций MongoDB, содержащих массивы, следует использовать расширение Виртуализации данных для Azure Data Studio для создания инструкции CREATE EXTERNAL TABLE на основе схемы, обнаруженной драйвером ODBC PolyBase для MongoDB. Действия с плоской выполняются драйвером автоматически. Кроме того, можно использовать sp_data_source_objects (Transact-SQL) для обнаружения схемы коллекции (столбцов) и создания внешней таблицы вручную. Хранимая sp_data_source_table_columns процедура также автоматически выполняет преобразование в плоскую структуру с помощью драйвера ODBC PolyBase для MongoDB. Расширение Виртуализации данных для Azure Data Studio и sp_data_source_table_columns используйте те же внутренние хранимые процедуры для запроса внешней схемы.

Замечания по типу источника данных

  • Teradata

    Системные представления Teradata не используют безопасность на уровне строк (RLS), поэтому пользователи могут видеть наличие таблиц, которые они не могут запрашивать.

  • MongoDB

    Некоторые более ранние версии MongoDB ограничивают возможность перечисления всех баз данных для пользователей, таких как администратор. Пользователи без этого разрешения могут получить ошибки проверки подлинности, пытающиеся выполнить эту процедуру с помощью null @object_root_name.

  • Oracle

    Синонимы Oracle не поддерживаются для использования с PolyBase.

Примеры

SQL Server

В следующем примере возвращаются все базы данных, схемы и таблицы и представления

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

В следующем примере возвращается полная схемата и таблицы, функции, представления и т. д.

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]

MongoDB

В следующем примере возвращаются все базы данных и таблицы.

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]

Связанный контент