sp-columns-ex-transact-sql.md

title	sp_columns_ex (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	03/14/2017
ms.prod	sql
ms.prod_service	database-engine
ms.reviewer
ms.technology	system-objects
ms.topic	language-reference
f1_keywords	sp_columns_ex sp_columns_ex_TSQL
dev_langs	TSQL
helpviewer_keywords	sp_columns_ex
ms.assetid	c12ef6df-58c6-4391-bbbf-683ea874bd81
author	stevestein
ms.author	sstein

sp_columns_ex (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx-md]

Returns the column information, one row per column, for the specified linked server tables. sp_columns_ex returns column information for only the specific column if column is specified.

Transact-SQL Syntax Conventions

Syntax

  
sp_columns_ex [ @table_server = ] 'table_server'   
     [ , [ @table_name = ] 'table_name' ]   
     [ , [ @table_schema = ] 'table_schema' ]   
     [ , [ @table_catalog = ] 'table_catalog' ]   
     [ , [ @column_name = ] 'column' ]   
     [ , [ @ODBCVer = ] 'ODBCVer' ]  

Arguments

[ @table_server = ] 'table_server' Is the name of the linked server for which to return column information. table_server is sysname, with no default.

[ @table_name = ] 'table_name' Is the name of the table for which to return column information. table_name is sysname, with a default of NULL.

[ @table_schema = ] 'table_schema' Is the schema name of the table for which to return column information. table_schema is sysname, with a default of NULL.

[ @table_catalog = ] 'table_catalog' Is the catalog name of the table for which to return column information. table_catalog is sysname, with a default of NULL.

[ @column_name = ] 'column' Is the name of the database column for which to provide information. column is sysname, with a default of NULL.

[ @ODBCVer = ] 'ODBCVer' Is the version of ODBC that is being used. ODBCVer is int, with a default of 2. This indicates ODBC Version 2. Valid values are 2 or 3. For information about the behavior differences between versions 2 and 3, see the ODBC SQLColumns specification.

Return Code Values

None

Result Sets

Column name	Data type	Description
TABLE_CAT	sysname	Table or view qualifier name. Various DBMS products support three-part naming for tables (qualifier.owner.name). In [!INCLUDEssNoVersion] this column represents the database name. In some products, it represents the server name of the table's database environment. This field can be NULL.
TABLE_SCHEM	sysname	Table or view owner name. In [!INCLUDEssNoVersion], this column represents the name of the database user that created the table. This field always returns a value.
TABLE_NAME	sysname	Table or view name. This field always returns a value.
COLUMN_NAME	sysname	Column name, for each column of the TABLE_NAME returned. This field always returns a value.
DATA_TYPE	smallint	Integer value that correspond to ODBC type indicators. If this is a data type that cannot be mapped to an ODBC type, this value is NULL. The native data type name is returned in the TYPE_NAME column.
TYPE_NAME	varchar(13)	String representing a data type. The underlying DBMS presents this data type name.
COLUMN_SIZE	int	Number of significant digits. The return value for the PRECISION column is in base 10.
BUFFER_LENGTH	int	Transfer size of the data.1
DECIMAL_DIGITS	smallint	Number of digits to the right of the decimal point.
NUM_PREC_RADIX	smallint	Is the base for numeric data types.
NULLABLE	smallint	Specifies nullability. 1 = NULL is possible. 0 = NOT NULL.
REMARKS	varchar(254)	This field always returns NULL.
COLUMN_DEF	varchar(254)	Default value of the column.
SQL_DATA_TYPE	smallint	Value of the SQL data type as it appears in the TYPE field of the descriptor. This column is the same as the DATA_TYPE column, except for the datetime and SQL-92 interval data types. This column always returns a value.
SQL_DATETIME_SUB	smallint	Subtype code for datetime and SQL-92 interval data types. For other data types, this column returns NULL.
CHAR_OCTET_LENGTH	int	Maximum length in bytes of a character or integer data type column. For all other data types, this column returns NULL.
ORDINAL_POSITION	int	Ordinal position of the column in the table. The first column in the table is 1. This column always returns a value.
IS_NULLABLE	varchar(254)	Nullability of the column in the table. ISO rules are followed to determine nullability. An ISO SQL-compliant DBMS cannot return an empty string. YES = Column can include NULLS. NO = Column cannot include NULLS. This column returns a zero-length string if nullability is unknown. The value returned for this column is different from the value returned for the NULLABLE column.
SS_DATA_TYPE	tinyint	[!INCLUDEssNoVersion] data type, used by extended stored procedures.

For more information, see the Microsoft ODBC documentation.

Remarks

sp_columns_ex is executed by querying the COLUMNS rowset of the IDBSchemaRowset interface of the OLE DB provider corresponding to table_server. The table_name, table_schema, table_catalog, and column parameters are passed to this interface to restrict the rows returned.

sp_columns_ex returns an empty result set if the OLE DB provider of the specified linked server does not support the COLUMNS rowset of the IDBSchemaRowset interface.

Permissions

Requires SELECT permission on the schema.

Remarks

sp_columns_ex follows the requirements for delimited identifiers. For more information, see Database Identifiers.

Examples

The following example returns the data type of the JobTitle column of the HumanResources.Employee table in the [!INCLUDEssSampleDBobject] database on the linked server Seattle1.

EXEC sp_columns_ex 'Seattle1',   
   'Employee',   
   'HumanResources',   
   'AdventureWorks2012',   
   'JobTitle';  

See Also

sp_catalogs (Transact-SQL)
sp_foreignkeys (Transact-SQL)
sp_indexes (Transact-SQL)
sp_linkedservers (Transact-SQL)
sp_primarykeys (Transact-SQL)
sp_tables_ex (Transact-SQL)
sp_table_privileges (Transact-SQL)
System Stored Procedures (Transact-SQL)