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  quot MySQL quot  and  quot MS SQL Server quot   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

User · Answer

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 · Answer

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

User · Answer

Stolen from here   USE YOURDBNAME GO  SELECT   FROM sys Tables GO

User · Answer

For Mysql you can do simple    SHOW TABLES

User · Answer

Exec sp MSforeachtable  Select

User · Answer

select   from sys tables order by schema id      --comments  order by  schema id  to get the  tables  in  object explorer order  go

User · Answer

USE DBName  SELECT   FROM sys Tables    We can deal without GO in-place of you can use semicolon

User · Answer

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

User · Answer

In order if someone would like to list all tables within specific database without using the  quot use quot  keyword  SELECT TABLE NAME FROM databasename INFORMATION SCHEMA TABLES

User · Answer

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

User · Answer

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

User · Answer

I did not see this answer but hey this is what I do    SELECT name FROM databaseName sys Tables

User · Answer

This works Fine SELECT   FROM information schema tables

User · Answer

In mysql  use   SHOW TABLES    After selecting the DB with   USE db name

User · Answer

The following query will select all of the Tables in the database named DBName   USE DBName GO  SELECT   FROM sys Tables GO

User · Answer

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

User · Answer

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 · Answer

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 · 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

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

Examples related to sql

Examples related to sql-server