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

Question

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

Navigation

#1 by (3 votes)

4

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

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

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

The following (incorrect) query illustrates what I want 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 could not adapt it to MySQL.

mysql sql-select query select

asked by anonymous 26.09.2014 / 19:08

1 answer

Java interface layout How to create functions with callback?

score 3 · Accepted Answer

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 queries you want to run, listing all tables and counting with SELECT * FROM <tabela>

To execute these queries, you can create a procedure that will execute the querys returned by the previous query. Maybe there is some better way to structure this procedure (parameterize the limits and query the cursor for example), but the way it 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