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

user_300118 0
Viewed 1465877

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?

sql-server sql
21 Answers

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
user_60585160

This works Fine

SELECT * FROM information_schema.tables;

user_68261910

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.

Query

user_58867120

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.

user_68046260

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.

user_65429430

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;
user_11217420 
SELECT TABLE_NAME
FROM your_database_name.INFORMATION_SCHEMA.TABLES
ORDER BY TABLE_NAME;
user_37289010

for postgres it will be:

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