sp_tables Catalog Stored Procedures

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

Syntax

sp_tables [table_name] [, table_owner] [, table_qualifier] [, table_type]

where

table_name

Specifies the table used to return catalog information. The table_name can be a variable character length name with a maximum of 32 characters. Wildcard pattern matching is supported.

table_owner

Specifies the table owner of the table used to return catalog information. The table_owner can be a variable character name with a maximum of 32 characters. Wildcard pattern matching is supported. If the table_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, that table's columns are returned. If the table_owner is not specified and the current user does not own a table with the specified table_name, this procedure will look for a table with the specified table_name owned by the database owner. If one exists, that table's columns are returned.

table_qualifier

Is the name of the table qualifier. 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.

table_type (varchar(60))

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/or 'VIEW'.

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

Remarks

For maximum interoperability, the gateway client should assume only ANSI-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 results 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 results set for sp_server_info, only tables that are accessible by the current user are returned.

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

This is the results set: