- 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.
SQL SERVER:
In SQL Server, we have four different ways to list all the tables in a database.
SELECT table_nameFROM INFORMATION_SCHEMA.TABLESWHERE table_type = 'BASE TABLE'SELECT nameFROM sys.tablesSELECT nameFROM sysobjectsWHERE xtype = 'U'SELECT nameFROM sys.objectsWHERE 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_nameFROM dba_tables-- This returns all the tables which are accessible to the current userSELECT table_nameFROM all_tables-- This returns all the tables which are created by the current userSELECT table_nameFROM user_tables
MySQL:
In MySQL, we can use the below query to list all the tables in the server.
-- Lists all the tables in all databasesSELECT table_nameFROM information_schema.tablesWHERE table_type='BASE TABLE'-- Lists all the tables in a particular databaseSELECT table_nameFROM information_schema.tablesWHERE table_type='BASE TABLE' AND table_schema = 'your_database_name'