t43CuuN3

1SELECT DISTINCT SCHEMA_NAME AS `database`
2    FROM information_schema.SCHEMATA
3   WHERE  SCHEMA_NAME NOT IN ('information_schema', 'performance_schema', 'mysql')
4   ORDER BY SCHEMA_NAME
5	
6SELECT TABLE_SCHEMA AS `database`,
7         TABLE_NAME AS `table`
8    FROM information_schema.TABLES
9   WHERE TABLE_TYPE = 'BASE TABLE'
10   ORDER BY TABLE_SCHEMA, TABLE_NAME
11	
12DELIMITER $$
13DROP PROCEDURE IF EXISTS `zap_magento_logs`$$
14
15CREATE PROCEDURE `zap_magento_logs`()
16BEGIN
17
18    -- declare variables for database and table names
19    DECLARE dbname VARCHAR(128) DEFAULT '';
20    DECLARE tbname VARCHAR(128) DEFAULT '';
21
22    DECLARE done INTEGER DEFAULT 0;
23
24    -- declare cursor for list of log tables
25    DECLARE log_table_list CURSOR FOR 
26      SELECT TABLE_SCHEMA AS `database`,
27             TABLE_NAME AS `table`
28        FROM `information_schema`.TABLES
29       WHERE TABLE_TYPE = 'BASE TABLE'
30         AND TABLE_NAME IN 
31         (
32            'log_customer',
33        'log_visitor',
34        'log_visitor_info',
35        'log_url',
36        'log_url_info',
37        'log_quote'
38         )
39       ORDER BY TABLE_SCHEMA, TABLE_NAME;
40
41    -- declare NOT FOUND handler
42        DECLARE CONTINUE HANDLER 
43        FOR NOT FOUND SET done = 1;
44
45    OPEN log_table_list;
46
47    log_table: LOOP
48
49        FETCH log_table_list INTO dbname, tbname;
50
51        IF done = 1 THEN
52        LEAVE log_table;
53        END IF;
54
55        -- create an appropriate text string for a DDL or other SQL statement
56        SET @s = CONCAT('SELECT COUNT(*) AS num FROM  ',dbname,'.',tbname);
57        PREPARE stmt FROM @s;
58        EXECUTE stmt;
59        DEALLOCATE PREPARE stmt; 
60    END LOOP    log_table;
61    CLOSE log_table_list;
62
63END$$
64
65DELIMITER ;
66	
67CALL zap_magento_logs();
68	
69$tables = array(
70    'log_customer',
71    'log_visitor',
72    'log_visitor_info',
73    'log_url',
74    'log_url_info',
75    'log_quote',
76    'report_viewed_product_index',
77    'report_compared_product_index',
78    'report_event',
79    'catalog_compare_item',
80);
81
82$dbh = new PDO('mysql:host=localhost;', 'USERNAME', 'PASSWORD', array(
83    PDO::ATTR_PERSISTENT => true
84));
85
86
87$sql = $dbh->query('SHOW DATABASES');
88$getAllDbs = $sql->fetchALL(PDO::FETCH_ASSOC);
89
90foreach ($getAllDbs as $DB) {  
91
92        foreach ($tables as $table) {
93            $dbh->query('TRUNCATE TABLE ' . $DB['Database'] . '.' . $table);
94
95    };
96};
97	
98$db = mysqli_connect(); // your database connection
99
100$tables = ["log_customer", "log_visitor", "log_visitor_info"]; // array with all the tables
101
102foreach ($tables as $table) {
103    mysqli_query($db, "TRUNCATE TABLE `".$table."`"); // executes query for each element in the array
104}
105	
106SET sql_mode='PIPES_AS_CONCAT';
107
108select 
109'truncate table ' || dbs.database || '.someLogTable;'
110 as ''
111from  (SELECT DISTINCT SCHEMA_NAME AS `database`
112         FROM information_schema.SCHEMATA
113        WHERE SCHEMA_NAME NOT IN ('information_schema', 'performance_schema', 'mysql', 'test')
114        ORDER BY SCHEMA_NAME) as dbs;
115	
116mysql -u root -p < createSomeSQL.sql > sqlToExecute.sql