In SQL Server, you can use the sp_tables system stored procedure to get a list of tables and views in the current environment.

You can return all tables and views, or you can narrow it down to a particular owner, type, pattern, or even a specific table or view.

Syntax

The syntax goes like this:

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

All arguments are optional.

Example 1 – No Arguments

You can execute this stored procedure without any arguments. Doing this will return all tables and views in the current environment.

Like this:

EXEC sp_tables;

This returns over 500 rows on my system, so I won’t provide the results here. As well as returning user-defined tables and views, it also returns system objects, such as the sys and INFORMATION_SCHEMA tables and views.

Example 2 – All Arguments

At the other extreme, here’s an example that includes all arguments. This narrows the results down to a specific table name, a specific type, a specific qualifier, and a specific owner.

EXEC sp_tables
  @table_name = 'Customers',
  @table_owner = 'Sales',
  @table_qualifier = 'WideWorldImporters',
  @table_type = "'TABLE'",
  @fUsePattern = 1;

Result:

+--------------------+---------------+--------------+--------------+-----------+
| TABLE_QUALIFIER    | TABLE_OWNER   | TABLE_NAME   | TABLE_TYPE   | REMARKS   |
|--------------------+---------------+--------------+--------------+-----------|
| WideWorldImporters | Sales         | Customers    | TABLE        | NULL      |
+--------------------+---------------+--------------+--------------+-----------+

Example 3 – Return a Specific Table

An easier way to return a specific table is to simply use the first argument.

Like this:

EXEC sp_tables @table_name = 'Customers';

Or even more concisely, like one of these:

EXEC sp_tables 'Customers';
sp_tables 'Customers';

Although, be aware that you could get more than one row. In this example, two rows are returned:

+--------------------+---------------+--------------+--------------+-----------+
| TABLE_QUALIFIER    | TABLE_OWNER   | TABLE_NAME   | TABLE_TYPE   | REMARKS   |
|--------------------+---------------+--------------+--------------+-----------|
| WideWorldImporters | Sales         | Customers    | TABLE        | NULL      |
| WideWorldImporters | Website       | Customers    | VIEW         | NULL      |
+--------------------+---------------+--------------+--------------+-----------+

The first row is for a table and the second row is for a view.

If I wasn’t interested in seeing any views or system tables, I could add one more argument to specify the table type.

Example 4 – Return a Specific Table Type

Here, I refine the previous example, by specifying only the table type that I’m interested in, along with the name of the table.

EXEC sp_tables 
  @table_name = 'Customers',
  @table_type = "'TABLE'";

Result:

+--------------------+---------------+--------------+--------------+-----------+
| TABLE_QUALIFIER    | TABLE_OWNER   | TABLE_NAME   | TABLE_TYPE   | REMARKS   |
|--------------------+---------------+--------------+--------------+-----------|
| WideWorldImporters | Sales         | Customers    | TABLE        | NULL      |
+--------------------+---------------+--------------+--------------+-----------+

The acceptable types include VIEW, TABLE, and SYSTEMTABLE.

Note that table types must be uppercase.

Example 5 – Return Multiple Table Types

You might’ve noticed that the syntax for the table type uses single quotes and double quotes. This is because it accepts a comma separated list of table types. The whole list is enclosed within double quotes, and each list item is enclosed in single quotes, with a comma separating each item.

Here’s an example of returning table types TABLE and VIEW.

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

Note that if SET QUOTED_IDENTIFIER is ON, each single quotation mark must be doubled and the whole parameter must be enclosed in single quotation marks.

Example 6 – A Note about the Table Qualifier Argument

If you use the @table_qualifier argument, its value must be the same as the current environment, otherwise you’ll get an error. In SQL Server, the table qualifier represents the database name. In some products, it represents the server name of the table’s database environment.

Here’s what happens in SQL Server if I use a value that’s different to the current database:

USE Music;
EXEC sp_tables 
  @table_qualifier = 'WideWorldImporters';

Result:

Msg 15250, Level 16, State 1, Procedure sp_tables, Line 86
The database name component of the object qualifier must be the name of the current database.

Example 7 – Wildcards

You can use the @fUsePattern argument to specify whether the underscore ( _ ), percent ( % ), and bracket ( [ or ] ) characters are interpreted as wildcard characters. Valid values are 0 (pattern matching is off) and 1 (pattern matching is on). The default value is 1.

Here’s an example of using pattern matching to return table names that begin with the letter “A”:

EXEC sp_tables
  @table_name = 'A%',
  @table_type = "'TABLE'",
  @fUsePattern = 1;

Result:

+-------------------+---------------+--------------+--------------+-----------+
| TABLE_QUALIFIER   | TABLE_OWNER   | TABLE_NAME   | TABLE_TYPE   | REMARKS   |
|-------------------+---------------+--------------+--------------+-----------|
| Music             | dbo           | Albums       | TABLE        | NULL      |
| Music             | dbo           | Artists      | TABLE        | NULL      |
+-------------------+---------------+--------------+--------------+-----------+

But here’s what happens if I disable pattern matching:

EXEC sp_tables
  @table_name = 'A%',
  @table_type = "'TABLE'",
  @fUsePattern = 0;

Result:

(0 rows affected)