sp-tables-ex-transact-sql.md

title	sp_tables_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_tables_ex sp_tables_ex_TSQL
dev_langs	TSQL
helpviewer_keywords	sp_tables_ex
ms.assetid	33755c33-7e1e-4ef7-af14-a9cebb1e2ed4
author	stevestein
ms.author	sstein

sp_tables_ex (Transact-SQL)

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

Returns table information about the tables from the specified linked server.

Transact-SQL Syntax Conventions

Syntax

  
sp_tables_ex [ @table_server = ] 'table_server'   
     [ , [ @table_name = ] 'table_name' ]   
     [ , [ @table_schema = ] 'table_schema' ]  
     [ , [ @table_catalog = ] 'table_catalog' ]   
     [ , [ @table_type = ] 'table_type' ]   
     [ , [@fUsePattern = ] 'fUsePattern' ]  

Arguments

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

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

[ @table_schema = ] 'table_schema'] Is the table schema. table_schemais sysname, with a default of NULL.

[ @table_catalog = ] 'table_catalog' Is the name of the database in which the specified table_name resides. table_catalog is sysname, with a default of NULL.

[ @table_type = ] 'table_type' Is the type of the table to return. table_type is sysname, with a default of NULL, and can have one of the following values.

Value	Description
ALIAS	Name of an alias.
GLOBAL TEMPORARY	Name of a temporary table available system wide.
LOCAL TEMPORARY	Name of a temporary table available only to the current job.
SYNONYM	Name of a synonym.
SYSTEM TABLE	Name of a system table.
SYSTEM VIEW	Name of a system view.
TABLE	Name of a user table.
VIEW	Name of a view.

[ @fUsePattern = ] 'fUsePattern' Determines whether the characters _, %, [, and ] are interpreted as wildcard characters. Valid values are 0 (pattern matching is off) and 1 (pattern matching is on). fUsePattern is bit, with a default of 1.

Return Code Values

None

Result Sets

Column name	Data type	Description
TABLE_CAT	sysname	Table qualifier name. Various DBMS products support three-part naming for tables (qualifier.owner.name). In [!INCLUDEssNoVersion], this column represents the database name. In some other products, it represents the server name of the database environment of the table. This field can be NULL.
TABLE_SCHEM	sysname	Table owner name. In [!INCLUDEssNoVersion], this column represents the name of the database user who created the table. This field always returns a value.
TABLE_NAME	sysname	Table name. This field always returns a value.
TABLE_TYPE	varchar(32)	Table, system table, or view.
REMARKS	varchar(254)	[!INCLUDEssNoVersion] does not return a value for this column.

Remarks

sp_tables_ex is executed by querying the TABLES 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_tables_ex returns an empty result set if the OLE DB provider of the specified linked server does not support the TABLES rowset of the IDBSchemaRowset interface.

Permissions

Requires SELECT permission on the schema.

Examples

The following example returns information about the tables that are contained in the HumanResources schema in the [!INCLUDEssSampleDBobject] database on the LONDON2 linked server.

EXEC sp_tables_ex @table_server = 'LONDON2',   
@table_catalog = 'AdventureWorks2012',   
@table_schema = 'HumanResources',   
@table_type = 'TABLE';  

See Also

Distributed Queries Stored Procedures (Transact-SQL)
sp_catalogs (Transact-SQL)
sp_columns_ex (Transact-SQL)
sp_column_privileges (Transact-SQL)
sp_foreignkeys (Transact-SQL)
sp_indexes (Transact-SQL)
sp_linkedservers (Transact-SQL)
sp_table_privileges (Transact-SQL)
System Stored Procedures (Transact-SQL)