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