Chapter 2: Catalog Stored Procedures

sp_tables

Returns a list of objects that can appear in a from clause.

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

table_name: is the name of the table. Use wildcard characters to request information about more than one table.

table_owner: is the table owner. Use wildcard characters to request information about more than one table.

table_qualifier: is the name of the database. Acceptable values are the name of the current database and NULL.

table_type

is a list of values, separated by commas, giving information about all tables of the table type(s) specified, including the following:

"'TABLE', 'SYSTEM TABLE', 'VIEW'"

Enclose each table type with single quotation marks, and enclose the entire parameter with double quotation marks. Enter table types in uppercase.

sp_tables @table_type = "'TABLE', 'VIEW'"

This procedure returns information about all tables in the current database of the type TABLE and VIEW and excludes information about system tables.

Adaptive Server does not necessarily check the read and write permissions on table_name. Access to the table is not guaranteed, even if you can display information about it.
The results set includes tables, views, and synonyms and aliases for gateways to DBMS products.
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 results set for sp_tables is:

Column	Datatype	Description
table_qualifier	varchar(30)	The database name. This field can be NULL.
table_owner	varchar(30)
table_name	varchar(30)	NOT NULL. The table name.
table_type	varchar(32)	NOT NULL. One of the following: 'TABLE', 'VIEW', 'SYSTEM TABLE'.
remarks	varchar(254)	NULL

Any user can execute sp_tables.

master.dbo.sysattributes, master.dbo.sysloginroles, master.dbo.syssrvroles, sysroles