SQL - Show Tables (Listing Tables)

---

There are several instances when you need to retrieve a list of tables from your database. This could be done for testing purposes, to identify any existing tables before adding or removing any, or for any other reason. You can also use these queries to display all tables in your database if you've forgotten the name of a particular table or how to spell a table.

The command to display all tables in a particular database is unique to each database system. When we have several databases with different types of tables, the SHOW TABLE statement is crucial. This statement is extremely helpful when multiple databases share the same table names.

Listing Tables in SQL Server

SQL Server does not provide SHOW TABLE command in an SQL Server. Instead, we can use the "SELECT" statement to retrieve information about tables in a database. We have three different commands to use with the SELECT statement to list all the tables in a database −

• sys.tables

• schema.tables

• sysobjects

The databases such as PostgreSQL, DB2 and Oracle use the commands ‘\dt’, ‘db2 list tables for all’ and ‘all_tables’ respectively to list all the tables in the database. Whereas, the database MySQL uses the command ‘Show Table’ to list all the tables present in it.

Using SQL sys.tables view

We can use the sys.tables view in SQL to list all the tables in a database. It returns a record of each table in the current database containing its metadata (information) about each table. The metadata includes the name of the table, its unique identifier, the schema it belongs to, and the date and time when it was created, etc.

Syntax

Following is the syntax to list all the tables in SQL using sys.tables −

SELECT * FROM sys.tables;

Example

Following is the query to list all the tables in SQL Server −

SELECT * FROM sys.tables;

Output

Following is the output of the above query −

+-------------------+-----------------+--------------+-------------+
| name              | object_id       | principal_id | schema_id   | ...
+-------------------+-----------------+--------------+-------------+
|CUSTOME            | 4195065         | NULL         |   1         | 
|ORDERS             | 68195293        | NULL         |	 1        |
|CUSTOMERS          | 100195407       | NULL         |	 1        |
|spt_fallback_db    | 117575457       | NULL         |	 1        |
|spt_fallback_dev   | 13357551        | NULL         |	 1        |
|spt_fallback_usg   | 1431676148      | NULL         |	 1        |
|EMPLOYEE           | 2107154552      | NULL         |	 1        |
|Xyz                | 1803153469      | NULL         |	 1        |
|CARS               | 1511676433      | NULL         |	 1        |
|spt_monitor        | 149575571       | NULL         |	 1        |
+-------------------+-----------------+--------------+-------------+

Here, ‘...’ represents that this table contains many more columns such as- ‘parent_object_id’, ‘type’, ‘type_desc’, ‘create_date’, ‘modify_date’ and so on.

Using SQL schema.tables view

We can also use schema.tables view to retrieve a list of tables in a specific schema. In SQL Server, a schema is a container that holds database objects such as tables, views, and stored procedures.

Syntax

Following is the syntax to list all the tables in a specific schema −

SELECT * FROM schema_name.tables

Example

Following is the query to retrieve the list of all the tables in the information_schema present in the database −

SELECT table_name, table_type
FROM information_schema.tables;

Output

The result produced is as follows −

+----------------------+------------+
|table_name            | table_type |
+----------------------+------------+
|CUSTOME               | BASE TABLE |
|ORDERS                | BASE TABLE |
|CUSTOMERS             | BASE TABLE |
|spt_fallback_db       | BASE TABLE |
|spt_fallback_dev      | BASE TABLE |
|spt_fallback_usg      | BASE TABLE |
|EMPLOYEE              | BASE TABLE |
|CARS                  | BASE TABLE |
|spt_values            | VIEW       |
|spt_monitor           | BASE TABLE |
|MSreplication_options | BASE TABLE |
+----------------------+------------+

Note − We can also retrieve a specific subset of tables based on some criteria, such as the name, owner, or creation date of the tables with the help of a WHERE clause.

Example

In here, we are trying to retrieve all the base tables (tables which contains metadata) from information schema using the WHERE clause −

SELECT table_name, table_schema
FROM information_schema.tables
WHERE table_type = 'BASE TABLE';

Output

Following is the output of the above query −

+----------------+--------------------+
| TABLE_NAME     | TABLE_SCHEMA       |
+----------------+--------------------+
| agent          | hello              |
| agentdemo      | hello              |
| car            | hello              |
| customers      | hello              |
| department     | hello              |
| employee       | hello              |
| fruit          | hello              |
| student        | hello              |
| variables_info | performance_schema |
| sys_config     | sys                |
+----------------+--------------------+

Using SQL sysobjects view

We can use sysobjects view to retrieve the information of all the objects created in the database, including stored procedures, views, system tables and user-defined tables.

Syntax

Following is the basic syntax of using sysobjects view −

SELECT * FROM sysobjects

Example

Following is the query to retrieve all the user created table from the database. Here, xtype specifies the type of tables to be retrieved such as, ‘U’ which stands for user created table: −

SELECT name, id, xtype
FROM sysobjects
WHERE xtype = 'U';

Output

The output produced is as follows −

+-----------------+------------+-------+
| name            | id         | xtype |
+-----------------+------------+-------+
|Agentdemo        | 4195065    | U     | 
|XYZ              | 68195293   | U     |
|Fruit            | 100195407  | U     |
|Car              | 117575457  | U     |
|Workers          | 13357551   | U     |
|CUSTOMER_BACKUP  | 1431676148 | U     |
|CUSTOMER_COLUMN  | 2107154552 | U     |
|CUSTOMER_COLUMN1 | 1803153469 | U     |
|customer_salary  | 1511676433 | U     |
|customer_salary1 | 149575571  | U     |
|Agent            | 2099048    | U     |
+-----------------+------------+-------+

List of value for xtype

You can also use the following list of other object types to filter your search for different objects in the database −

AF − Aggregate function (CLR)

C − CHECK constraint

D − Default or DEFAULT constraint

F − FOREIGN KEY constraint

L − Log

FN − Scalar function

FS − Assembly (CLR) scalar-function

FT − Assembly (CLR) table-valued function

IF − In-lined table-function

IT − Internal table

P − Stored procedure

PC − Assembly (CLR) stored-procedure

PK − PRIMARY KEY constraint (type is K)

RF − Replication filter stored procedure

S − System table

SN − Synonym

SQ − Service queue

TA − Assembly (CLR) DML trigger

TF − Table function

TR − SQL DML Trigger

TT − Table type

U − User table

UQ − UNIQUE constraint (type is K)

V − View

X − Extended stored procedure