MySQL Forums
Forum List  »  Stored Procedures

Count Table rows using Stored Procedure
Posted by: Alvaro Ullrich
Date: February 08, 2018 11:01AM

Help needed here. I'm trying to get mySQL do the following:

1- Read a list of tables (e.g. 10)
2- Count the no. of rows in each
3- Print the result (after each count, or as a whole at the end).

Best I've got is this SP:


------
CREATE DEFINER=`your.name`@`192.168.%` PROCEDURE `test`(name_table varchar(50))
BEGIN

SET @sqlText = CONCAT('SELECT COUNT(*) FROM ', name_table,' ;') ;

PREPARE stmt FROM @sqlText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END
-----

If you provide the table name, it gets the job done. OK for one table, but not for 200. My problems are 2:

1) How do you copy the result of executing @sqlText *to a variable*, and then print it?

2) How do you iterate a table (or any other repository) so that you can put *all tables* into one place? I want to store the whole result so that it can be easily compared.
I've heard of cursors, still unsure how to use them in mySQL.

Thanks in advance,

Options: ReplyQuote


Subject
Views
Written By
Posted
Count Table rows using Stored Procedure
9732
February 08, 2018 11:01AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.