sp_tables_ex (T-SQL)

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

Syntax

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

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_name is sysname, with a default of NULL.

[@table_schema =] 'table_schema']

Is the table schema. table_schema is 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 these 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
TABLE	Name of a user table
VIEW	Name of a view

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 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 owner name. In SQL Server, 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)	SQL Server 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

Execute permission default to the public role.

Examples

This example returns table information about the titles table in the pubs database, on the LONDON2 linked server.

USE master

EXEC sp_tables_ex 'LONDON2', 'titles', 'dbo', 'pubs', NULL

sp_catalogs	sp_indexes
sp_columns_ex	sp_linkedservers
sp_column_privileges	sp_table_privileges
sp_foreignkeys	System Stored Procedures