Edit

sp_data_source_table_columns (Transact-SQL)

  • Article

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, the recommended data type lengths for varchar data type columns in external tables may 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

Related content