CODEWITHSUNDEEP
sqlsql-server
Awan
Awan

Reputation: 18560

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

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?

Upvotes: 435

Views: 1754709

Answers (21)

Ashish Gupta
Ashish Gupta

Reputation: 1241

For MySQL, you can do this:

SHOW TABLES;

Upvotes: 4

Dario Cimmino
Dario Cimmino

Reputation: 163

SELECT name FROM databaseName.sys.Tables;

Upvotes: 7

Dhiren Biren
Dhiren Biren

Reputation: 530

SELECT * FROM information_schema.tables;

Upvotes: 12

Md. Kamrul Hassan
Md. Kamrul Hassan

Reputation: 40

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

Upvotes: 1

Vy Do
Vy Do

Reputation: 52516

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

Upvotes: 2

AGR
AGR

Reputation: 351

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

Upvotes: 12

Patel Nikhil
Patel Nikhil

Reputation: 305

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;

Upvotes: 0

Md. Jamal Uddin
Md. Jamal Uddin

Reputation: 792

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.

Upvotes: 2

Tarit Ray
Tarit Ray

Reputation: 994

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

Upvotes: 7

Tyler
Tyler

Reputation: 426

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.

Upvotes: 1

anishMarokey
anishMarokey

Reputation: 11397

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

USE DBName
GO 
SELECT *
FROM sys.Tables
GO

Upvotes: 42

Gopal00005
Gopal00005

Reputation: 2151

USE DBName;
SELECT * FROM sys.Tables;

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

Upvotes: 20

Ivan
Ivan

Reputation: 4753

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.

Upvotes: 1

Jason L.
Jason L.

Reputation: 1215

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

Upvotes: 0

Lorenzo Lerate
Lorenzo Lerate

Reputation: 3870

In mysql, use:

SHOW TABLES;

After selecting the DB with:

USE db_name

Upvotes: 15

Michael Baylon
Michael Baylon

Reputation: 6740

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.

Upvotes: 670

amanda
amanda

Reputation: 21

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

Upvotes: 2

kayakpim
kayakpim

Reputation: 995

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.

Upvotes: 0

Amir Keshavarz
Amir Keshavarz

Reputation: 3108

Exec sp_MSforeachtable 'Select ''?'''

Upvotes: 1

David S
David S

Reputation: 211

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'

Upvotes: 21

bla
bla

Reputation: 5480

Stolen from here:

USE YOURDBNAME
GO 
SELECT *
FROM sys.Tables
GO

Upvotes: 108

Related Questions