Skip to content

sp-data-source-table-columns.md

Latest commit

 

History

History
120 lines (85 loc) · 5.01 KB

sp-data-source-table-columns.md

File metadata and controls

120 lines (85 loc) · 5.01 KB
title sys.sp_data_source_table_columns (Transact-SQL)
description sp_data_source_table_columns returns a list of columns in external data source table.
author rwestMSFT
ms.author randolphwest
ms.date 01/28/2026
ms.service sql
ms.subservice system-objects
ms.topic reference
f1_keywords
sp_data_source_table_columns_TSQL
sys.sp_data_source_table_columns_TSQL
sp_data_source_table_columns
sys.sp_data_source_table_columns
helpviewer_keywords
PolyBase
dev_langs
TSQL

sys.sp_data_source_table_columns (Transact-SQL)

[!INCLUDE sqlserver2019]

Returns a list of columns in external data source table.

Note

This procedure is introduced in SQL 2019 CU5.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

sys.sp_data_source_table_columns
    [ @data_source = ] N'data_source'
    , [ @table_location = ] N'table_location'
    [ , [ @column_name = ] N'column_name' ]
    [ , [ @search_options = ] N'search_options' ]
[ ; ]

Arguments

[ @data_source = ] N'data_source'

The name of the external data source to get the metadata from. @data_source is sysname, with no default.

[ @table_location = ] N'table_location'

The table location string that identifies the table. @table_location is nvarchar(max), with no default.

[ @column_name = ] N'column_name'

[!INCLUDE ssinternalonly-md]

[ @search_options = ] N'search_options'

[!INCLUDE ssinternalonly-md]

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. 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 [!INCLUDE sssql19-md], 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 AS SYSNAME = N'ExternalDataSourceName';
DECLARE @table_location AS NVARCHAR (400) = N'[database].[schema].[table]';

EXECUTE sp_data_source_table_columns
    @data_source,
    @table_location;

Related content