Get all table names of a particular database by SQL query?
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?
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
.
Read more… Read less…
The following query will select all of the Tables
in the database named DBName
:
USE DBName
GO
SELECT *
FROM sys.Tables
GO
USE DBName;
SELECT * FROM sys.Tables;
We can deal without GO
in-place of you can use semicolon ;
.
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'