count records containing specific word from ALL tables.

[87dave87]

I have a database containing 40+ tables (and counting), Im looking for the code to do a row count for all the records in every table of the database which contain 'windows' in the field 'os'?

[Jessica]

Did you try SELECT count(*) AS theCount FROM * WHERE os = 'windows';

[87dave87]

 

 

MySQL said:

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* WHERE os = 'windows'' at line 1

[Jessica]

You might have to go through all the tables and do the selects, then add up the results.

[effigy]

This might look prettier in PHP (or another language), but this should pass as a pure SQL solution:

 

DROP PROCEDURE IF EXISTS tally_os;
DELIMITER $$
CREATE PROCEDURE tally_os(IN var_os VARCHAR(20))
MAIN_BLOCK: BEGIN
DECLARE var_total INT UNSIGNED DEFAULT 0;
DECLARE var_table VARCHAR(255);
DECLARE var_no_more_tables BOOLEAN DEFAULT FALSE;
DECLARE cursor_tables CURSOR FOR SHOW TABLES;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET var_no_more_tables = TRUE;
OPEN cursor_tables;
TABLES: LOOP
	FETCH cursor_tables INTO var_table;
	IF var_no_more_tables THEN
		CLOSE cursor_tables;
		LEAVE TABLES;
	END IF;
	SET @sql = CONCAT('SELECT COUNT(*) FROM ', var_table, ' WHERE os = "', var_os, '" INTO @var_count');
	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	SET var_total = var_total + @var_count;
	DEALLOCATE PREPARE stmt;
	SET @var_count = 0;
END LOOP TABLES;
SELECT var_total;
END MAIN_BLOCK;
$$
DELIMITER ;

 

Run with: CALL tally_os('windows');