Get all table names of a particular database by SQL query?
SqlSql ServerSql Problem Overview
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 Solutions
Solution 1 - Sql
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.
Solution 2 - Sql
Stolen from here:
USE YOURDBNAME
GO
SELECT *
FROM sys.Tables
GO
Solution 3 - Sql
The following query will select all of the Tables in the database named DBName:
USE DBName
GO
SELECT *
FROM sys.Tables
GO
Solution 4 - Sql
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'
Solution 5 - Sql
USE DBName;
SELECT * FROM sys.Tables;
We can deal without GO in-place of you can use semicolon ;.
Solution 6 - Sql
In mysql, use:
SHOW TABLES;
After selecting the DB with:
USE db_name
Solution 7 - Sql
I did not see this answer but hey this is what I do :
SELECT name FROM databaseName.sys.Tables;
Solution 8 - Sql
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
Solution 9 - Sql
This works Fine
SELECT * FROM information_schema.tables;
Solution 10 - Sql
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.
Solution 11 - Sql
For Mysql you can do simple. SHOW TABLES;
Solution 12 - Sql
select * from sys.tables
order by schema_id --comments: order by 'schema_id' to get the 'tables' in 'object explorer order'
go
Solution 13 - Sql
Exec sp_MSforeachtable 'Select ''?'''
Solution 14 - Sql
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.
Solution 15 - Sql
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.
Solution 16 - Sql
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.
Solution 17 - Sql
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.
Solution 18 - Sql
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
Solution 19 - Sql
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;
Solution 20 - Sql
SELECT TABLE_NAME
FROM your_database_name.INFORMATION_SCHEMA.TABLES
ORDER BY TABLE_NAME;
Solution 21 - Sql
for postgres it will be:
SELECT table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'your_schema' -- probably public