How to display all the tables from a database in SQL?
- How-Tos FAQs
- December 17, 2018
Different databases have different system views or catalogs to list all the tables in a database. Let us see some ways to check this in a few database systems.
How to display all the tables from a database in SQL
SQL SERVER:
In SQL Server, we have four different ways to list all the tables in a database.
SELECT table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'BASE TABLE'
SELECT name
FROM sys.tables
SELECT name
FROM sysobjects
WHERE xtype = 'U'
SELECT name
FROM sys.objects
WHERE type_desc = 'USER_TABLE'Oracle
In Oracle, we have three different ways to list all the tables in a database.
-- This returns all the tables in the database system.
SELECT table_name
FROM dba_tables
-- This returns all the tables which are accessible to the current user
SELECT table_name
FROM all_tables
-- This returns all the tables which are created by the current user
SELECT table_name
FROM user_tablesMySQL:
In MySQL, we can use the below query to list all the tables in the server.
-- Lists all the tables in all databases
SELECT table_name
FROM information_schema.tables
WHERE table_type='BASE TABLE'
-- Lists all the tables in a particular database
SELECT table_name
FROM information_schema.tables
WHERE table_type='BASE TABLE'
AND table_schema = 'your_database_name'