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?

This question is similar to: How do I get list of all tables in a database using TSQL?. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. — starball
– starball ♦, Commented Jan 10 at 19:40

Ian Kemp - SO dead by AI greed · Accepted Answer · 2015-04-02 08:17:36Z

672

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.

edited Apr 2, 2015 at 8:17

Ian Kemp - SO dead by AI greed

1

answered Oct 12, 2010 at 11:16

Michael Baylon

6,7601 gold badge17 silver badges2 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Verena Praher Over a year ago

I prefer this solution when I have different table schemata within one database (SQL Server): SELECT CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='MyDB'

phougatv Over a year ago

One must be using that particular DB to get the information. USE dbName GO for SQL-Server. If you're not using the DB, result will not be displayed, even if there are tables in that DB.

Ashish Gupta Over a year ago

For Mysql you can do simple. SHOW TABLES;

Mmm Over a year ago

The SQL Server entry works fine for me without the USE dbName GO prefix on Server 2014.

Apostolos Over a year ago

1) You have suggested the same thing for an SQL Server and MySQL 2) INFORMATION_SCHEMA.TABLES exists only in MySQL 3) I really wonder how comes you got so many upvotes for this ...

|

bla · Accepted Answer · 2010-10-12 10:27:16Z

108

Stolen from here:

USE YOURDBNAME
GO 
SELECT *
FROM sys.Tables
GO

answered Oct 12, 2010 at 10:27

bla

5,4703 gold badges27 silver badges26 bronze badges

4 Comments

om471987 Over a year ago

Most probably SELECT Name FROM sys.Tables where is_ms_shipped = 0

cjb110 Over a year ago

this is database provider specific, and not ANSI SQL compatible.

buckminst Over a year ago

Alternatively, SELECT * FROM yourdbname.sys.Tables; if you prefer to be more concise. Works in SQL Server, at least.

Max Rios Over a year ago

Careful with using sys.Tables, you can end up with multiple names because you are not taking into account the schema. Two tables exactly the same name with different schemas will show twice

Cody Gray · Accepted Answer · 2017-10-03 16:03:35Z

42

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

USE DBName
GO 
SELECT *
FROM sys.Tables
GO

edited Oct 3, 2017 at 16:03

Cody Gray♦

246k53 gold badges511 silver badges590 bronze badges

answered Oct 12, 2010 at 10:26

anishMarokey

11.4k2 gold badges36 silver badges47 bronze badges

2 Comments

anishMarokey Over a year ago

NO it only Part seperator. you can change to other.Its not an T-SQL syntax.

Hamzeh Soboh Over a year ago

USE DatabaseSample SELECT * FROM sys.Tables

Arman H · Accepted Answer · 2014-05-21 00:01:29Z

21

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'

edited May 21, 2014 at 0:01

Arman H

5,64610 gold badges54 silver badges77 bronze badges

answered May 20, 2014 at 23:35

David S

2112 silver badges2 bronze badges

Comments

Community · Accepted Answer · 2017-04-19 10:37:01Z

20

USE DBName;
SELECT * FROM sys.Tables;

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

edited Apr 19, 2017 at 10:37

CommunityBot

11 silver badge

answered Sep 22, 2014 at 5:33

Gopal00005

2,1715 gold badges36 silver badges54 bronze badges

1 Comment

Jonah Over a year ago

For SQL Server on Azure, this worked for me, but the accepted answer didn't. Thanks.

Lorenzo Lerate · Accepted Answer · 2015-08-29 13:23:41Z

15

In mysql, use:

SHOW TABLES;

After selecting the DB with:

USE db_name

answered Aug 29, 2015 at 13:23

Lorenzo Lerate

3,9303 gold badges28 silver badges25 bronze badges

4 Comments

Deepa MG Over a year ago

Osm,,, swt n short

Melle Over a year ago

This is MySQL, question is tagged wit MSSQL server

Lorenzo Lerate Over a year ago

@Melle Look at the description of the question

Chiwda Over a year ago

This is not the best answer, see my comment under the accepted (and correct ) answer.

AGR · Accepted Answer · 2020-08-12 08:29:37Z

12

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

edited Aug 12, 2020 at 8:29

answered Aug 5, 2020 at 12:55

AGR

3516 silver badges8 bronze badges

1 Comment

Arqam Rafay Over a year ago

You will get all the table by using above query, If you want to just get table which is in public > table . Then add SELECT TABLE_NAME FROM postgres.INFORMATION_SCHEMA.tables where table_schema = public Remember using postgres

CPPUIX · Accepted Answer · 2024-05-19 20:30:10Z

12

SELECT * FROM information_schema.tables;

edited May 19, 2024 at 20:30

CPPUIX

2,4178 gold badges17 silver badges38 bronze badges

answered Aug 18, 2020 at 18:50

Dhiren Biren

5305 silver badges8 bronze badges

Comments

CPPUIX · Accepted Answer · 2024-05-19 20:35:37Z

9

SELECT name FROM databaseName.sys.Tables;

edited May 19, 2024 at 20:35

CPPUIX

2,4178 gold badges17 silver badges38 bronze badges

answered Jun 14, 2017 at 18:39

Dario Cimmino

1832 silver badges9 bronze badges

Comments

Tarit Ray · Accepted Answer · 2019-11-26 15:41:28Z

7

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.

answered Nov 26, 2019 at 15:41

Tarit Ray

1,00413 silver badges24 bronze badges

Comments

CPPUIX · Accepted Answer · 2024-05-19 20:36:08Z

4

For MySQL, you can do this:

SHOW TABLES;

edited May 19, 2024 at 20:36

CPPUIX

2,4178 gold badges17 silver badges38 bronze badges

answered Nov 8, 2016 at 0:38

Ashish Gupta

1,24913 silver badges15 bronze badges

Comments

serenesat · Accepted Answer · 2015-03-25 09:23:20Z

2

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

edited Mar 25, 2015 at 9:23

serenesat

4,72210 gold badges41 silver badges53 bronze badges

answered Oct 29, 2014 at 2:00

amanda

211 bronze badge

1 Comment

Alex K Over a year ago

Any chance you could elaborate a bit on what you mean by this? Maybe explain it?

Md. Jamal Uddin · Accepted Answer · 2019-12-02 10:05:14Z

2

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.

answered Dec 2, 2019 at 10:05

Md. Jamal Uddin

7908 silver badges17 bronze badges

Comments

Vy Do · Accepted Answer · 2022-04-25 06:58:54Z

2

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

answered Apr 25, 2022 at 6:58

Vy Do

53.4k70 gold badges266 silver badges397 bronze badges

Comments

Amir Keshavarz · Accepted Answer · 2014-09-22 07:41:26Z

1

Exec sp_MSforeachtable 'Select ''?'''

edited Sep 22, 2014 at 7:41

answered Sep 22, 2014 at 5:37

Amir Keshavarz

3,1181 gold badge22 silver badges26 bronze badges

2 Comments

bummi Over a year ago

Hi Amirreza, I believe you are talking about sp_MSforeachtable ?

Amir Keshavarz Over a year ago

@bummi: yes great,Thank you for attention and sorry for typing mistake. Edited

Ivan · Accepted Answer · 2016-06-20 10:56:31Z

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.

Tyler · Accepted Answer · 2018-07-18 11:32:38Z

1

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.

answered Jul 18, 2018 at 11:32

Tyler

4264 silver badges12 bronze badges

1 Comment

onur demir Over a year ago

I just realized that this query give wrong result for non-english characters in table name while Michael Baylon's gives correct results. (character was in Turkish language lowercase "ı" )

Md. Kamrul Hassan · Accepted Answer · 2022-11-08 04:31:56Z

1

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

answered Nov 8, 2022 at 4:31

Md. Kamrul Hassan

401 silver badge4 bronze badges

1 Comment

Yunnosch Over a year ago

While this code may solve the question, including an explanation of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please edit your answer to add explanations and give an indication of what limitations and assumptions apply.

serenesat · Accepted Answer · 2015-03-25 09:02:08Z

0

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.

edited Mar 25, 2015 at 9:02

serenesat

4,72210 gold badges41 silver badges53 bronze badges

answered Apr 11, 2014 at 6:00

kayakpim

9932 gold badges11 silver badges28 bronze badges

2 Comments

cjb110 Over a year ago

they're looking for cross provider queries not provider specific.

kayakpim Over a year ago

There is no consistent way to do this from sql as all db engines implement the data dictionary differently. It might be possible to abstract this using jdbc/odbc and a 3GL language such as C/Java/C# if this is a programmatic requirement, definitely possible if you have a different implementation for each database. The op should clarify their requirements...

Jason L. · Accepted Answer · 2015-12-09 15:48:54Z

0

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

answered Dec 9, 2015 at 15:48

Jason L.

1,21513 silver badges19 bronze badges

Comments

Patel Nikhil · Accepted Answer · 2020-02-07 06:05:26Z

0

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;

answered Feb 7, 2020 at 6:05

Patel Nikhil

3251 silver badge6 bronze badges

Collectives™ on Stack Overflow

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

21 Answers 21

6 Comments

4 Comments

2 Comments

Comments

1 Comment

4 Comments

1 Comment

Comments

Comments

Comments

Comments

1 Comment

Comments

Comments

2 Comments

Comments

1 Comment

1 Comment

2 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

21 Answers 21

6 Comments

4 Comments

2 Comments

Comments

1 Comment

4 Comments

1 Comment

Comments

Comments

Comments

Comments

1 Comment

Comments

Comments

2 Comments

Comments

1 Comment

1 Comment

2 Comments

Comments

Comments

Linked

Related