Sql Enquiry To Detect All Tabular Array Names On A Database Alongside Mysql As Well As Sql Server Examples

How practise you lot honour names of all tables inwards a database is a recent  SQL interview questions asked to 1 of my friend. There are many ways to honour all tabular array names cast whatever database similar MySQL as well as SQL Server. You tin strength out 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 interrogation similar when to work truncate as well as delete or correlated vs noncorrelated subquery which you lot tin strength out hold back simply about all candidate laid good but this is quite mutual if you lot are working on whatever database e.g. MySQL. In this SQL tutorial nosotros volition run into examples of getting names of all tables from MySQL as well as SQL Server database. In MySQL at that topographic point are 2 ways to honour names of all tables, either past times using "show" keyword or  by query INFORMATION_SCHEMA. In  case of SQL Server or MSSQL, You tin strength out either work sys.tables or INFORMATION_SCHEMA to instruct all tabular array names for a database. By the means if you lot are novel inwards MySQL server as well as exploring it , you lot may honour this listing of frequently used MySQL server commands handy.

How to honour cite of all tables inwards MySQL database

Let's run into 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 inwards laid (0.00 sec)

mysql> show tables;

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

| Tables_in_test |

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

| department     |

| employee       |

| role           |

| user           |

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

4 rows inwards laid (0.00 sec)

While working inwards MySQL database I prefer to work show tables later on selecting database e.g  use database_name. Compact as well as intuitive syntax of show keyword makes it slowly to show all tabular array names of selected database from simply unproblematic query. On the other manus using INFORMATION_SCHEMA  is to a greater extent than full general means as well as you lot tin strength out fifty-fifty exceed cite of database to SELECT query.

How to honour cite of all tables inwards SQL Server database

In final subdivision nosotros accept seen SQL query to present names of all tables inwards MySQL database as well as right away nosotros volition run into SQL query illustration for SQL Server database. Here is illustration of getting all tabular array names inwards 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 strength out work sys.tables to instruct all tabular array names from selected database equally shown inwards next SQL query

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

That's all on how to honour all tabular array names from database inwards MySQL as well as SQL Server. I volition update this article if I constitute 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 live on there. allow me know if whatever of you lot guys tried to honour names of all tables from electrical current database inwards PostgreSQL.

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

Share this post