locked
Find table name on the database using stored procedure on MySql 8.0.17 RRS feed

  • Question

  • User-1453200658 posted

    Hi,

    This is a list name of tables stored a database MySql version 8.0.17

    +------------------+
    | listTable        |
    +------------------+
    | Table_A2_11_2021 |
    | Table_L7_12_2021 |
    | Table_C3_1_2021  |
    | Table_D8_10_2021 |
    | Table_T0_11_2021 |
    | Table_E9_3_2021  |
    | Table_L4_2_2021  |
    | Table_O1_12_2021 |
    | Table_P2_5_2021  |
    | Table_Q2_10_2021 |
    | Table_A3_12_2021 |
    | Table_S5_9_2021  |
    | Table_T8_11_2021 |
    | Table_Q6_1_2021  |
    +------------------+
    

    The table name storage policy is

    • Table_
    • Western alphabet letter (issued by an algorithm that recognizes the connected user, privileges, etc.)_
    • Random number_
    • Month Number_
    • Current Year

    I need find on the database MySql all the table for first Western alphabet letter without Random_number for this return

    +-----------------+
    | listTable       |
    +-----------------+
    | Table_A_11_2021 |
    | Table_L_12_2021 |
    | Table_C_1_2021  |
    | Table_D_10_2021 |
    | Table_T_11_2021 |
    | Table_E_3_2021  |
    | Table_L_2_2021  |
    | Table_O_12_2021 |
    | Table_P_5_2021  |
    | Table_Q_10_2021 |
    | Table_A_12_2021 |
    | Table_S_9_2021  |
    | Table_T_11_2021 |
    | Table_Q_1_2021  |
    +-----------------+
    

    I have idea to use this Stored Procedure below but I just can't extract first Western alphabet letter without Random number.

    CREATE DEFINER=`root`@`%` PROCEDURE `SP_SIX_MONTHS`()
    BEGIN
    
    DECLARE tyear INT(4);
    DECLARE tmonth INT(2);
    
    SET tyear = YEAR(CURDATE());
    SET tmonth = MONTH(DATE_SUB(CURDATE(),INTERVAL 6 MONTH));
    
    SET @s = CONCAT('SELECT 
                     FROM information_schema.TABLES 
                     WHERE table_name LIKE ''table#_',???,'%#_',tmonth,'#_',tyear,''' ESCAPE ''#'';');                       
                                     
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    
    END

    Monday, March 8, 2021 3:52 PM

Answers