List PolyBase external tables in SQL Server database

This query works on SQL Server 2016 or newer.

SQL Server 2016 introduced new feature - PolyBase, that enables you to run queries on external data in Hadoop or to import/export data from Azure Blob Storage. External data is accesible through external tables.

Query below returns external PolyBase tables.

Query

select 
    schema_name(schema_id) as schema_name,
    t.name as table_name,
    s.name as source_name,
    s.location, 
    s.type_desc as source_type,
    f.name as format_name,
    f.format_type,
    f.field_terminator,
    f.string_delimiter,
    f.row_terminator,
    f.encoding,
    f.data_compression
from sys.external_tables t
    inner join sys.external_data_sources s
        on t.data_source_id = s.data_source_id
    inner join sys.external_file_formats f
        on t.file_format_id = f.file_format_id
order by schema_name, table_name

Columns

  • schema_name - external table schema name
  • table_name - external table name
  • source_name - name of the data source in the current database
  • location - the connection string, which includes the protocol, IP address, and port for the external data source
  • source_type - data source type:
    • HADOOP
    • RDBMS
    • SHARD_MAP_MANAGER
    • RemoteDataArchiveTypeExtDataSource
  • format_name - date file format name
  • format_type - file format type
    • DELIMITEDTEXT
    • RCFILE
    • ORC
    • PARQUET
  • field_terminator - field terminator (for format_type = DELIMITEDTEXT)
  • string_delimiter - string delimiter (for format_type = DELIMITEDTEXT)
  • row_terminator - character string that terminates each row in the external Hadoop file (for format_type = DELIMITEDTEXT)
  • encoding - encoding method for the external Hadoop file (for format_type = DELIMITEDTEXT)
  • data_compression - data compression method

Rows

  • One row represents one external table
  • Scope of rows: only external tables are included
  • Ordered by schema and table name

Sample results

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
0
There are no comments. Click here to write the first comment.