How to list a sample of all tables in a Mysql database?

Question

I need to sample all tables in a Mysql database, for example through a SELECT * ... LIMIT 10.

I already have the code that returns all tables of the current database:

select table_name from information_schema.tables where table_schema = database();

The following (incorrect) query illustrates what I wish to do:

Select * from ( 
    select table_name from information_schema.tables where table_schema = database()
 ) limit 10;

, which would function as:

Select * from tabela1 limit 10;
Select * from tabela2 limit 10;
...
Select * from tabelaN limit 10;

How can I do this in a single query?

I found examples that speak of cursors, but I’m not sure how I could apply them in this case.

I saw a very similar example in SQL Server using cursors, but I couldn’t adapt it to Mysql.

Answer 1

With the following query you will get what you need:

Select concat("SELECT * FROM ", table_name, " LIMIT 10;") 
FROM information_schema.tables 
WHERE table_schema = database()

It will return all the querys you wish to run, listing all the tables and contacting the SELECT * FROM <tabela>

To execute these querys, it is possible to create a procedure that will execute the querys returned by the previous query. Maybe there is a better way to structure this procedure (parameterize the limits and the cursor query for example), but the way it is works :)

DELIMITER $$

CREATE PROCEDURE `tablesPreview`()
BEGIN
    DECLARE done BOOL DEFAULT FALSE;
    DECLARE queryToExecute varchar(255);
    DECLARE tablesCursor
        CURSOR FOR
        SELECT concat("SELECT * FROM ", table_name, " LIMIT 10;") 
        FROM information_schema.tables 
        WHERE table_schema = database();

    DECLARE
        CONTINUE HANDLER FOR
        SQLSTATE '02000'
            SET done = TRUE;

    OPEN tablesCursor;

    myLoop: LOOP
        FETCH tablesCursor INTO queryToExecute;

        IF done THEN
            CLOSE tablesCursor;
            LEAVE myLoop;
        END IF;

    set @query = (SELECT queryToExecute);

    PREPARE tablePreviewQuery FROM @query;
    EXECUTE tablePreviewQuery;

    END LOOP;

END