INFORMATION_SCHEMA provides access to database metadata, (data dictionary or database catalog) such as the name of a database or table, the data type of a column, or access privileges. It is the information database, the place that stores information about all the other databases that the MySQL server maintains.
Here is an example of a statement that retrieves information from INFORMATION_SCHEMA.
mysql>
SELECT table_name, table_type FROM information_schema.tables WHERE table_schema = ‘yourdatabasename ORDER BY table_name DESC;
You can use stored procedure to do the same thing.
mysql>
DROP PROCEDURE GetAlltables;
DELIMITER /
CREATE PROCEDURE GetAlltables()
BEGIN
SELECT table_name FROM information_schema.tables WHERE table_schema = ‘yourdatabasename’ ORDER BY table_name DESC;
END /
DELIMITER ;
Then call
CALL GetAlltables();
You can use stored procedure to get all table counts.
DELIMITER /
CREATE PROCEDURE GetAlltablesCount()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE atablename CHAR(160);
DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = ‘yourdatabasename’ ORDER
BY table_name DESC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO atablename ;
IF NOT done THEN
SELECT @atablename , count(*) FROM @atablename b;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END /
DELIMITER ;
then call GetAlltablesCount();
to get all table counts.
Comment by bo — April 7, 2009 @ 7:53 am
There might be a problem with previous comment.
Here is the new one.
DELIMITER /
CREATE PROCEDURE GetAlltablesCount()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE atablename CHAR(160);
DECLARE cur1 CURSOR FOR SELECT table_name FROM information_schema.tables WHERE table_schema = ‘yourdatabasename’ ORDER BY table_name DESC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO atablename ;
IF NOT done THEN
SET @select_tablequery := CONCAT(”select ‘”,atablename, “‘,” , ‘count(*) from n2_development.’, atablename,’ ‘ , atablename);
PREPARE queryTable_stmt FROM @select_tablequery;
EXECUTE queryTable_stmt;
DEALLOCATE PREPARE queryTable_stmt;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END /
DELIMITER ;
Then call GetAlltablesCount();
to get all table counts.
Comment by bo — April 7, 2009 @ 8:02 am