sp_data_source_table_columns (Transact-SQL)
Applies to: 
SQL Server 2019 (15.x)
Returns list of columns in external data source table.
Note
This procedure is introduced in SQL 2019 CU5.
Transact-SQL syntax conventions
Syntax
sp_data_source_table_columns
[ @data_source = ] 'data_source'
, [ @table_location = ] 'table_location'
[ ; ]
Arguments
[ @data_source = ] 'data_source'
The name of the external data source to get the metadata from. Type is sysname.
[ @table_location = ] 'table_location'
The table location string that identifies the table. table_location type is nvarchar(max).
Result set
The stored procedure returns the following information:
| Column name | Data type | Description |
|---|---|---|
name |
nvarchar(max) | The name of the column. |
type |
nvarchar(200) | SQL Server type name. |
length |
int | Length of column. |
precision |
int | Precision of column. |
scale |
int | Scale of column. |
collation |
nvarchar(200) | SQL Server collation of column. |
is_nullable |
bit | 1 = nullable, 0 = not nullable. |
source_type_name |
nvarchar(max) | Backend-specific type name. Mostly used for debugging. For ODBC sources, source_type_name corresponds to the TYPE_NAME result column for SQLColumns(). |
remarks |
nvarchar(max) | General comments or description of column. Currently always NULL. |
Permissions
Requires ALTER ANY EXTERNAL DATA SOURCE permission.
Remarks
The SQL Server instance must have the PolyBase feature installed.
This stored procedure supports connectors for:
- SQL Server
- Oracle
- Teradata
- MongoDB
- Azure Cosmos DB
The stored procedure doesn't support generic ODBC data source or Hadoop connectors.
The notion of empty vs. non-empty relates to the behavior of the ODBC driver and the SQLTables function. Non-empty indicates an object contains tables, not rows. For example, an empty schema contains no tables in SQL Server. An empty database contains with no tables inside Teradata. The results are a SQL Server representation of the backend schema as interpreted by the PolyBase connector for the backend. The distinction here is that instead of merely passing along the results of the ODBC call to the backend, the results are based on the outcome of the PolyBase type-mapping code.
Use sp_data_source_objects and sp_data_source_table_columns to discover external objects. These system stored procedures return the schema of tables that are available to be virtualized. Azure Data Studio uses these two stored procedures to support data virtualization. Use sp_data_source_table_columns to discover external table schemas represented in SQL Server data types.
Due to differences between collations in Hadoop source data and supported collations in SQL Server 2019 (15.x), the recommended data type lengths for varchar data type columns in external tables might be much larger than expected. This is by design.
Oracle synonyms aren't supported for usage with PolyBase.
Examples
The following example returns the table columns for an external table in a SQL Server named server, belonging to a schema named schema.
DECLARE @data_source SYSNAME = N'ExternalDataSourceName';
DECLARE @table_location NVARCHAR(400) = N'[database].[schema].[table]';
EXEC sp_data_source_table_columns
@data_source,
@table_location;