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