How to selected record from selected column in queries mysql

Question

This is my fiddle https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=5f93901f015e087fb1f8466b7601e0b0

I have 3 tables like this

CREATE TABLE table1 
(
    ID int PRIMARY KEY NOT NULL, 
    endDate datetime
);

INSERT INTO table1 (ID, endDate)
VALUES (1, '2020-02-02');

CREATE TABLE table2 
(
     ID int PRIMARY KEY NOT NULL, 
     end_date datetime
);

INSERT INTO table2 (ID, end_date)
VALUES (1, '2020-03-02');

CREATE TABLE table3 
(
     ID int PRIMARY KEY NOT NULL, 
     EndDate datetime
);

INSERT INTO table3 (ID, EndDate)
VALUES (1, '2019-02-02');

Basically I want to find out in my database all of the columns with name contain 'end' with this query

SELECT DISTINCT TABLE_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE 'End%';

Result was like this :

+----------------------------------+--------------+
|            TABLE_NAME            | COLUMN_NAME  |
+----------------------------------+--------------+
| EVENTS                           | ENDS         |
| table1                           | endDate      |
| table2                           | end_date     |
| table3                           | EndDate      |
| events_stages_current            | END_EVENT_ID |
| events_stages_history            | END_EVENT_ID |
| events_stages_history_long       | END_EVENT_ID |
| events_statements_current        | END_EVENT_ID |
| events_statements_history        | END_EVENT_ID |
| events_statements_history_long   | END_EVENT_ID |
| events_transactions_current      | END_EVENT_ID |
| events_transactions_history      | END_EVENT_ID |
| events_transactions_history_long | END_EVENT_ID |
| events_waits_current             | END_EVENT_ID |
| events_waits_history             | END_EVENT_ID |
| events_waits_history_long        | END_EVENT_ID |
+----------------------------------+--------------+

Since in my database all of the columns with the name "end" are datetime data type, then I want to find out a record that in year = 2020 as the row, so the expected result was like this based on my fiddle

Expected results

+------------+--------------+---------------------+
| TABLE_NAME | COLUMNS_NAME |        DATA         |
+------------+--------------+---------------------+
| table1     | endDate      | 2020-02-02 00:00:00 |
| table2     | end_date     | 2020-03-02 00:00:00 |
+------------+--------------+---------------------+

The data that comes from table3 not included because that data exists in the year 2019 not 2020

You need to write a stored procedure that executes dynamic SQL with `PREPARE` and `EXECUTE`. — Barmar, Dec 31 '20 at 08:59
sorry, I'm just not having any idea how to solve this. please can you give me a clue about store procedure dynamic with prepare and execute — 18Man, Dec 31 '20 at 09:24
https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html — P.Salmon, Dec 31 '20 at 09:47

score 0 · Answer 1 · answered Dec 31 '20 at 09:21

0

Alternatively you could change your search pattern to `end%date':

SELECT DISTINCT TABLE_NAME, COLUMN_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME LIKE 'end%date';

answered Dec 31 '20 at 09:21

Carsten Massmann

26,510
2
22
43

and after i get that data, can I get the record for all those columns with several conditions? – 18Man Dec 31 '20 at 09:25

How to selected record from selected column in queries mysql

1 Answers1