Sql Question To Notice All Tabular Array Names On A Database Amongst Mysql In Addition To Sql Server Examples

How exercise you lot discovery names of all tables inward a database is a recent  SQL interview questions asked to 1 of my friend. There are many ways to discovery all tabular array names cast whatever database similar MySQL together with SQL Server. You tin instruct tabular array names either from INFORMATION_SCHEMA or sys.tables based upon whether you lot are using MySQL or Sql Server database. This is non a pop enquiry similar when to occupation truncate together with delete or correlated vs noncorrelated subquery which you lot tin aspect virtually all candidate laid upwards good but this is quite mutual if you lot are working on whatever database e.g. MySQL. In this SQL tutorial nosotros volition encounter examples of getting names of all tables from MySQL together with SQL Server database. In MySQL at that topographic point are 2 ways to discovery names of all tables, either past times using "show" keyword or  by query INFORMATION_SCHEMA. In  case of SQL Server or MSSQL, You tin either occupation sys.tables or INFORMATION_SCHEMA to instruct all tabular array names for a database. By the means if you lot are novel inward MySQL server together with exploring it , you lot may discovery this listing of frequently used MySQL server commands handy.

How to discovery advert of all tables inward MySQL database

Let's encounter illustration of using INFORMATION_SCHEMA  and present keyword to find all tabular array names from MySQL database:

mysql> SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' AND table_schema='test';

+------------+

| TABLE_NAME |

+------------+

| subdivision |

| employee   |

| role       |

| user       |

+------------+

4 rows inward laid (0.00 sec)

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| department     |

| employee       |

| role           |

| user           |

+----------------+

4 rows inward laid (0.00 sec)

While working inward MySQL database I prefer to occupation show tables afterwards selecting database e.g  use database_name. Compact together with intuitive syntax of show keyword makes it slow to show all tabular array names of selected database from only uncomplicated query. On the other mitt using INFORMATION_SCHEMA  is to a greater extent than full general means together with you lot tin fifty-fifty transcend advert of database to SELECT query.

How to discovery advert of all tables inward SQL Server database

In final subdivision nosotros bring seen SQL query to present names of all tables inward MySQL database together with similar a shot nosotros volition encounter SQL query illustration for SQL Server database. Here is illustration of getting all tabular array names inward MSSQL or SQL Server database:

USE test; //SELECT DATABASE
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'

or you lot tin occupation sys.tables to instruct all tabular array names from selected database equally shown inward next SQL query

USE test; //SELECT DATABASE
SELECT * FROM sys.tables

That's all on how to discovery all tabular array names from database inward MySQL together with SQL Server. I volition update this article if I establish similar SQL query for other database similar Oracle, Sybase or PostgreSQL etc. Though I haven't tried PostgreSQL but I am expecting show tables to operate there. allow me know if whatever of you lot guys tried to discovery names of all tables from electrical flow database inward PostgreSQL.

Further Learning
Introduction to SQL
The Complete SQL Bootcamp
SQL for Newbs: Data Analysis for Beginners

Share this post