sys.external_file_formats (Transact-SQL)
Applies to: 
SQL Server 2016 (13.x) and later
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
Contains a row for each external file format in the current database for SQL Server, SQL Database, and Azure Synapse Analytics.
Contains a row for each external file format on the server for Analytics Platform System (PDW).
| Column Name | Data Type | Description | Range |
|---|---|---|---|
| file_format_id | int | Object ID for the external file format. | |
| name | sysname | Name of the file format. in SQL Server and Azure Synapse Analytics, this is unique for the database. In Analytics Platform System (PDW), this is unique for the server. | |
| format_type | tinyint | The file format type. | DELIMITEDTEXT, RCFILE, ORC, PARQUET |
| field_terminator | nvarchar(10) | For format_type = DELIMITEDTEXT, this is the field terminator. | |
| string_delimiter | nvarchar(10) | For format_type = DELIMITEDTEXT, this is the string delimiter. | |
| date_format | nvarchar(50) | For format_type = DELIMITEDTEXT, this is the user-defined date and time format. | |
| use_type_default | bit | For format_type = DELIMITED TEXT, specifies how to handle missing values when PolyBase is importing data from HDFS text files into Azure Synapse Analytics. | 0 - store missing values as the string 'NULL'. 1 - store missing values as the column default value. |
| serde_method | nvarchar(255) | For format_type = RCFILE, this is the serialization/deserialization method. | |
| row_terminator | nvarchar(10) | For format_type = DELIMITEDTEXT, this is the character string that terminates each row in the external Hadoop file. | Always '\n'. |
| encoding | nvarchar(10) | For format_type = DELIMITEDTEXT, this is the encoding method for the external Hadoop file. | Always 'UTF8'. |
| data_compression | nvarchar(255) | The data compression method for the external data. | For format_type = DELIMITEDTEXT: - 'org.apache.hadoop.io.compress.DefaultCodec' - 'org.apache.hadoop.io.compress.GzipCodec' For format_type = RCFILE: - 'org.apache.hadoop.io.compress.DefaultCodec' For format_type = ORC: - 'org.apache.hadoop.io.compress.DefaultCodec' - 'org.apache.hadoop.io.compress.SnappyCodec' For format_type = PARQUET: - 'org.apache.hadoop.io.compress.GzipCodec' - 'org.apache.hadoop.io.compress.SnappyCodec' |
Permissions
The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration.
See Also
sys.external_data_sources (Transact-SQL)
sys.external_tables (Transact-SQL)
CREATE EXTERNAL FILE FORMAT (Transact-SQL)