sp_columns_ex
Returns the column information, one row per column, for the given linked server table(s). sp_columns_ex returns column information only for the given column if column is specified.
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 being used. ODBCVer is int, with a default of 2, indicating ODBC Version 2. Valid values are 2 or 3. Refer to the ODBC SQLColumns specification for the behavior differences between versions 2 and 3.
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 Microsoft® SQL Server™, 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 SQL Server, 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 corresponding to ODBC type indicators. If this is a data type that cannot be mapped to an ODBC type, it 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. |
| 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. 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 | SQL Server data type, used by Open Data Services extended stored procedures. For more information see Data Types. |
- 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
Execute permission defaults to the public role.
Examples
This example returns the data type of the title_id column of the titles table.
USE master
EXEC sp_columns_ex 'LONDON1', 'titles', 'dbo', 'pubs',
'title_id'