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

Question

I am working on application which can deal with multiple database servers like "MySQL" and "MS SQL Server".

I want to get tables' names of a particular database using a general query which should suitable for all database types. I have tried following:

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

But it is giving table names of all databases of a particular server but I want to get tables names of selected database only. How can I restrict this query to get tables of a particular database?

Answer 1

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.

Answer 2

Stolen from here:

USE YOURDBNAME
GO 
SELECT *
FROM sys.Tables
GO

Answer 3

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

USE DBName
GO 
SELECT *
FROM sys.Tables
GO

Answer 4

USE DBName;
SELECT * FROM sys.Tables;

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

Answer 5

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'

Answer 6

In mysql, use:

SHOW TABLES;

After selecting the DB with:

USE db_name

Answer 7

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

SELECT name FROM databaseName.sys.Tables;

Answer 8

For Mysql you can do simple. SHOW TABLES;

Answer 9

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

Answer 10

This works Fine

SELECT * FROM information_schema.tables;

Answer 11

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

Answer 12

Exec sp_MSforeachtable 'Select ''?'''

Answer 13

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.

Answer 14

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.

Answer 15

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.

Answer 16

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.

Answer 17

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.

Answer 18

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

Answer 19

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;