Advertisement
Advertisement


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?

2016/03/28
1
310
3/28/2016 8:33:18 PM

Accepted Answer

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.

2015/04/02
511
4/2/2015 8:17:36 AM

Stolen from here:

USE YOURDBNAME
GO 
SELECT *
FROM sys.Tables
GO
2010/10/12

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

USE DBName
GO 
SELECT *
FROM sys.Tables
GO
2017/10/03

USE DBName;
SELECT * FROM sys.Tables;

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

2017/04/19

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'
2014/05/21

In mysql, use:

SHOW TABLES;

After selecting the DB with:

USE db_name
2015/08/29

Source: https://stackoverflow.com/questions/3913620
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]