SQL - Show Tables

Last Updated : 03 May, 2024

When we are working with the SQL (Structured Query Language) Server database, understanding its structure is one of the fundamental tasks which is includes knowing which tables are available. Whether you are the database administrator, a developer or an analyst being able to list the tables within the database is a crucial skill.

In this article, we will develop the various methods to accomplish this task using the SQL queries in the SQL server. In SQL Server, there are different ways to list tables within the database such as using INFORMATION_SCHEMA.TABLES View, query system catalog views, dynamic management views (DMVs).

The syntax for the querying system views to list the tables in SQL Server:

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE';

Explanation:

We use a SELECT statement to retrieve the table names from INFORMATION_SCHEMA.TABLES View.
The WHERE clause is used to filter the results to include only those rows where the TABLE_TYPE column equals BASE TABLE, ensuring that only user-defined tables are included in the result set.

Example 1: Listing Tables in the Current Database

Step 1: Write the below code in SQL database to list tables in the current database

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE';

Output:

Serial Number	TABLE_NAME
1	spt_fallback_db
2	spt_fallback_dev
3	spt_fallback_usg
4	Empt
5	spt_monitor
6	MSreplication_options

The above output shows the list of the table names in the current database without including the system tables.

Explanation of the Code:

SELECT statement is used to SELECT the statement to query data from INFORMATION_SCHEMA.TABLES system view.
FROM clause is used to specify the INFORMATION_SCHEMA.TABLES view from the which you want to retrieve the data.
WHERE clause is used to filter to include only the rows where the TABLE_TYPE column equals BASE TABLE. This will be only user-defined tables are included in the result set.

Example 2: Listing Tables in a Specific Schema

Step 1: Write the below code in SQL database to list tables in the specific schema

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'dbo' 
AND TABLE_TYPE = 'BASE TABLE';

Output:

Serial Number	TABLE_NAME
1	spt_fallback_db
2	spt_fallback_dev
3	spt_fallback_usg
4	Empt
5	spt_monitor
6	MSreplication_options

The above output shows the list of table names in the specified schema i.e. dbo without including the system tables.

Explanation of the Code:

SELECT statement is used to SELECT the statement to query data from INFORMATION_SCHEMA.TABLES system view.
FROM clause is used to specify the INFORMATION_SCHEMA.TABLES view from the which you want to retrieve the data.
WHERE clause is used to filter to include only the rows where the TABLE_SCHEMA column equal to the dbo (dbo is the default schema in the SQL server ) and TABLE_TYPE column equals BASE TABLE.

Conclusion:

In conclusion, understanding the how to list tables in the SQL Server is the essential for the effectively managing the databases and performing the various data-related tasks. By the querying system views are like INFORMATION_SCHEMA.TABLES or catalog view, users can be retrieve the valuable metadata about the tables within the database including its names, types and schemas. We are using different methods in this article to retrieve the table names in the schema with detailed explanation and examples for the each approach. Whether the you are database administrator, or a developer, or an analyst mastering the above techniques you can navigate the database schemas analyse data structures and the process of streamline database management.