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.
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'
There are 3 different methods depending up on permissions you’ve got.
SELECT owner, table_name FROM dba_tables
SELECT owner, table_name FROM all_tables
SELECT table_name FROM user_tables