sp_tables

Returns a list of objects that can be queried in the current environment (any object that can appear in a FROM clause).

Syntax

sp_tables [ [ @table_name = ] 'name' ]
    [ , [ @table_owner = ] 'owner' ]
    [ , [ @table_qualifier = ] 'qualifier' ]
    [ , [ @table_type = ] "type" ]

Arguments

[@table_name =] 'name'

Is the table used to return catalog information. name is nvarchar(384), with a default of NULL. Wildcard pattern matching is supported.

[@table_owner =] 'owner'

Is the table owner of the table used to return catalog information. owner is nvarchar(384), with a default of NULL. Wildcard pattern matching is supported. If the owner is not specified, the default table visibility rules of the underlying DBMS apply.

In Microsoft® SQL Server™, if the current user owns a table with the specified name, the columns of that table are returned. If the owner is not specified and the current user does not own a table with the specified name, this procedure looks for a table with the specified name owned by the database owner. If one exists, the columns of that table are returned.

[@table_qualifier =] 'qualifier'

Is the name of the table qualifier. qualifier is sysname, with a default of NULL. 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.

[,[@table_type =] "'type'"]

Is a list of values, separated by commas, that gives information about all tables of the table type(s) specified, including TABLE, SYSTEM TABLE, and VIEW. type is varchar(100), with a default of NULL.

Note  Single quotation marks must surround each table type, and double quotation marks must enclose the entire parameter. Table types must be uppercase. If SET QUOTED_IDENTIFIER is ON, each single quotation mark must be doubled and the entire parameter must be surrounded by single quotation marks.

Return Code Values

None

Result Sets

Column name	Data type	Description
TABLE_QUALIFIER	sysname	Table qualifier name. In SQL Server, this column represents the database name. This field can be NULL.
TABLE_OWNER	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

For maximum interoperability, the gateway client should assume only SQL-92-standard SQL pattern matching (the % and _ wildcards).

Privilege information about the current user's read or write access to a specific table is not always checked, so access is not guaranteed. This result set includes not only tables and views, but also synonyms and aliases for gateways to DBMS products that support those types. If the server attribute ACCESSIBLE_TABLES is Y in the result set for sp_server_info, only tables that are accessible by the current user are returned.

sp_tables is equivalent to SQLTables in ODBC. The results returned are ordered by TABLE_TYPE, TABLE_QUALIFIER, TABLE_OWNER, and TABLE_NAME.

Permissions

Execute permission default to public role.

Examples

A. Return a list of objects that can be queried in the current environment

EXEC sp_tables

B. Return information about the syscolumns table in the Company database

EXEC sp_tables syscolumns, dbo, Company, "'SYSTEM TABLE'"