sp_tables

Description

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

Syntax

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

Parameters

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'"

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

Examples

Example 1

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.

Usage

Permissions

Any user can execute sp_tables.

Tables used

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