Sql Interrogation To Uncovering All Tabular Array Names On A Database Amongst Mysql Together With Sql Server Examples

How exercise you lot notice names of all tables inwards a database is a recent  SQL interview questions asked to i of my friend. There are many ways to notice all tabular array names shape whatever database similar MySQL as well as SQL Server. You tin give the sack larn 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 query similar when to exercise truncate as well as delete or correlated vs noncorrelated subquery which you lot tin give the sack human face virtually all candidate gear upward good but this is quite mutual if you lot are working on whatever database e.g. MySQL. In this SQL tutorial nosotros volition run across examples of getting names of all tables from MySQL as well as SQL Server database. In MySQL at that spot are ii ways to notice names of all tables, either past times using "show" keyword or  by query INFORMATION_SCHEMA. In  case of SQL Server or MSSQL, You tin give the sack either exercise sys.tables or INFORMATION_SCHEMA to larn all tabular array names for a database. By the agency if you lot are novel inwards MySQL server as well as exploring it , you lot may notice this listing of frequently used MySQL server commands handy.

How to notice advert of all tables inwards MySQL database

Let's run across representative of using INFORMATION_SCHEMA  and demo 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 exercise show tables later on selecting database e.g  use database_name. Compact as well as intuitive syntax of show keyword makes it slow to show all tabular array names of selected database from simply uncomplicated query. On the other mitt using INFORMATION_SCHEMA  is to a greater extent than full general agency as well as you lot tin give the sack fifty-fifty exceed advert of database to SELECT query.

How to notice advert of all tables inwards SQL Server database

In terminal subdivision nosotros convey seen SQL query to demo names of all tables inwards MySQL database as well as at i time nosotros volition run across SQL query representative for SQL Server database. Here is representative 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 give the sack exercise sys.tables to larn 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 notice all tabular array names from database inwards MySQL as well as SQL Server. I volition update this article if I flora 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 notice 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

Sumber https://javarevisited.blogspot.com/

Share this post