Get all table names of a particular database by SQL query?

Probably due to the way different sql dbms deal with schemas.

Try the following

For SQL Server:

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

For MySQL:

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

For Oracle I think the equivalent would be to use DBA_TABLES.

Stolen from here:

USE YOURDBNAME
GO 
SELECT *
FROM sys.Tables
GO

The following query will select all of the Tables in the database named DBName:

USE DBName
GO 
SELECT *
FROM sys.Tables
GO

Just put the DATABASE NAME in front of INFORMATION_SCHEMA.TABLES:

select table_name from YOUR_DATABASE.INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'

USE DBName;
SELECT * FROM sys.Tables;

We can deal without GO in-place of you can use semicolon ;.

In mysql, use:

SHOW TABLES;

After selecting the DB with:

USE db_name

I did not see this answer but hey this is what I do :

SELECT name FROM databaseName.sys.Tables;

In order if someone would like to list all tables within specific database without using the "use" keyword:

SELECT TABLE_NAME FROM databasename.INFORMATION_SCHEMA.TABLES

This works Fine

SELECT * FROM information_schema.tables;

To select the database query below :

use DatabaseName

Now

SELECT * FROM INFORMATION_SCHEMA.TABLES

Now you can see the created tables below in console .

PFA.

https://i.stack.imgur.com/Sp26S.png

For Mysql you can do simple. SHOW TABLES;

select * from sys.tables
order by schema_id      --comments: order by 'schema_id' to get the 'tables' in 'object explorer order'
go

Exec sp_MSforeachtable 'Select ''?'''

USE dbName;

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_SCHEMA = 'dbName' OR TABLE_SCHEMA = 'schemaName')
ORDER BY TABLE_NAME

If you are working with multiple schemata on an MS SQL server, then SELECT-ing TABLE_NAME without also simultaneously selecting TABLE_SCHEMA might be of limited benefit, so I have assumed we are interested in the tables belonging to a known schema when using MS SQL Server.

I have tested the query above with SQL Server Management Studio using an SQL Server database of mine and with MySQL Workbench using a MySQL database, and in both cases it gives the table names.

The query bodges Michael Baylon's two different queries into one that can then run on either database type. The first part of the WHERE clause works on MySQL databases and the second part (after the OR) works on MS SQL Server databases. It is ugly and logically a little incorrect as it supposes that there is no undesired schema with the same name as the database. This might help someone who is looking for one single query that can run on either database server.

UPDATE FOR THE LATEST VERSION OF MSSQL SERVER (17.7)

SELECT name FROM sys.Tables WHERE type_desc = 'USER_TABLE'

Or SELECT * for get all columns.

In our Oracle DB (PL/SQL) below code working to get the list of all exists tables in our DB.

select * from tab;

and

select table_name from tabs;

both are working. let's try and find yours.

Yes oracle is :

select * from user_tables

That is if you only want objects owned by the logged in user/schema otherwise you can use all_tables or dba_tables which includes system tables.

Building from Michael Baylon's answer, I needed a list which also included schema information and this is how I modified his query.

SELECT TABLE_SCHEMA + '.' + TABLE_NAME as 'Schema.Table'
  FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'dbName'
  ORDER BY TABLE_SCHEMA, TABLE_NAME

Simply get all improtanat information with this below SQL in Mysql

    SELECT t.TABLE_NAME , t.ENGINE , t.TABLE_ROWS ,t.AVG_ROW_LENGTH, 
t.INDEX_LENGTH FROM 
INFORMATION_SCHEMA.TABLES as t where t.TABLE_SCHEMA = 'YOURTABLENAMEHERE' 
order by t.TABLE_NAME ASC limit 10000;

SELECT TABLE_NAME
FROM your_database_name.INFORMATION_SCHEMA.TABLES
ORDER BY TABLE_NAME;

for postgres it will be:

SELECT table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema  = 'your_schema' -- probably public