Get all table names of a particular database.

While executing a script through a database, recently I came with a situation where I need to find how many tables exist in the database. I searched it and thought it will be helpful if I put the query for all mostly used database in one place.

For MySQL:

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

For MS SQL:

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

For Oracle:

There are 3 different methods depending up on permissions you’ve got.

SELECT owner, table_name FROM dba_tables

OR

SELECT owner, table_name FROM all_tables

OR

SELECT table_name FROM user_tables

Leave a Reply

Your email address will not be published.