Find Table

Description


You need to connect to a remote database and extract some information from it. The problem is, you don’t know the name of the most important table in this database! You were told that it should start with the letter e and end with the letter s. Now you want to find all possible candidates, i. e. tables with such names, as well as their column names and their datatypes. It is guaranteed that at least one such table exists in the database.

You have already connected to the database named ri_db. Now you just need to implement a procedure that will find the information about the most important table in it, as described above. The resulting table should have the following structure:

  • tab_name: the name of the found table;
  • col_name: the name of the column in the found table;
  • data_type: the type of this column.

The rows in the output should be sorted first by the name of the table, then by the column names.

Example

For the following tables events

event_date event_name
2016-10-08 Mum’s birthday
2016-10-31 Halloween

and guestlist in the database

id first_name surname
1 John Miller
2 Evelyn Ross

the output should be

tab_name col_name data_type
events event_date date
events event_name varchar
  • [execution time limit] 10 seconds (mysql)

Solution


1
2
3
4
5
6
7
8
/*Please add ; after each select statement*/
CREATE PROCEDURE findTable()
BEGIN
	SELECT table_name AS tab_name, column_name AS col_name, data_type
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_schema='ri_db' AND table_name REGEXP '^e.*s$'
    ORDER BY  tab_name, col_name;
END