8Hwmk1TJ

1CREATE DATABASE IF NOT EXISTS test;
2DROP PROCEDURE IF EXISTS test.SearchUsedTables;
3DELIMITER //
4CREATE PROCEDURE test.SearchUsedTables(dbname varchar(64),start_date DATE, end_date DATE, table_prefix VARCHAR(512))
5BEGIN
6    
7    -- DECLARE i INT DEFAULT 1;
8    SET @var_table_list = "";
9    
10    DROP TABLE IF EXISTS od_table_list;
11    CREATE TABLE od_table_list 
12    (
13        id INT(11) UNSIGNED AUTO_INCREMENT  PRIMARY KEY ,
14        table_names VARCHAR(255) 
15    );
16    
17    SET @sql_table_lists = "";
18    SET @sql_table_lists = CONCAT(" INSERT INTO od_table_list(table_names)
19                                    SELECT DISTINCT TABLE_NAME 
20                                    FROM INFORMATION_SCHEMA.COLUMNS 
21                                    WHERE TABLE_SCHEMA = '",dbname,"' 
22                                    AND COLUMN_NAME = 'modified_date' ");
23                                                
24    PREPARE STMT FROM @sql_table_lists;
25    EXECUTE STMT;
26    DEALLOCATE PREPARE STMT;       
27   
28    SET @var_string_length = "";
29    SELECT COUNT(1) INTO @var_string_length 
30    FROM od_table_list;
31    
32    DROP  TABLE IF EXISTS od_temp_table_list;
33    
34    CREATE  TABLE od_temp_table_list
35    (
36        table_name VARCHAR(512)
37    );
38    
39    SET @i = 1;
40    
41    WHILE (@i <= @var_string_length )  DO 
42        SET @var_table = "";
43        
44        IF(table_prefix  = '') THEN
45            SELECT table_names INTO @var_table FROM od_table_list WHERE id = @i;
46        
47        ELSE 
48            SET @sql_table_prefix = "";
49            SET @sql_table_prefix = CONCAT("SELECT table_names INTO @var_table FROM od_table_list WHERE id = @i AND table_names LIKE '%",table_prefix,"%' ");
50            
51            PREPARE STMT FROM @sql_table_prefix;
52            EXECUTE STMT;
53            DEALLOCATE PREPARE STMT;     
54        END IF;
55          
56        IF(IFNULL(@var_table,'') !='') THEN
57        
58                SET @var_count = "";
59                
60                SET @sql_select = "";
61                SET @sql_select = CONCAT("SELECT COUNT(1) INTO @var_count 
62                                        FROM  ",dbname,".",@var_table,"
63                                        WHERE CAST(modified_date AS DATE) BETWEEN '",start_date,"' AND '",end_date,"' ");
64                                        
65                PREPARE STMT FROM @sql_select;
66                EXECUTE STMT;
67                DEALLOCATE PREPARE STMT;                            
68                
69                IF(@var_count) > 0 THEN 
70                    SET @sql_insert = CONCAT("  INSERT INTO od_temp_table_list(table_name)
71                                                VALUES('",@var_table,"')");
72                    PREPARE STMT FROM @sql_insert;
73                    EXECUTE STMT;
74                    DEALLOCATE PREPARE STMT;  
75                END IF;
76                
77          END IF;
78          
79    SET @i = @i + 1;
80    END WHILE ;
81    
82    SELECT * FROM od_temp_table_list;
83END //
84DELIMITER ;