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.
SQL – Show Tables
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.