CODEWITHSUNDEEP
mysql
dotancohen
dotancohen

Reputation: 31481

Select 3 results from every table

Assuming a database with a ridiculous amount of tables (200+), how can I perform SELECT * FROM <> LIMIT 3; where <> represents all the tables in the database? My goal is to get an idea of what each table contains, and the column names shown in DESCRIBE are not particularly useful. Therefore I would like to see 3 records from each table.

I know that I could easily script this in PHP by iterating over the output of show tables; however I am looking for a command to run on the MySQL interpreter (mysql> prompt).

Upvotes: 0

Views: 115

Answers (1)

fancyPants
fancyPants

Reputation: 51878

It's described in detail under this link (haven't tried it myself though, it's just in my bookmarks):

http://www.youdidwhatwithtsql.com/mysql-clone-of-sp_msforeachtable/624

DELIMITER $$

DROP PROCEDURE IF EXISTS `usp_mysql_foreachtable`$$

CREATE PROCEDURE `usp_mysql_foreachtable`(IN sql_string VARCHAR(1000))
    LANGUAGE SQL
    NOT DETERMINISTIC
    COMMENT 'Functional clone of sp_MsForEachTable'
    BEGIN

    DECLARE var_tablename VARCHAR(100);
    DECLARE last_row BIT;

    DECLARE table_cursor CURSOR FOR SELECT TABLE_NAME
                    FROM information_schema.TABLES
                    WHERE TABLE_TYPE = 'BASE TABLE'
                    AND TABLE_SCHEMA = DATABASE();

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET last_row = 1;

    OPEN table_cursor;
    FETCH table_cursor INTO var_tablename;

    SET last_row = 0;
    SET @var = '';

    lbl_table_cursor: LOOP

        SET @qry = REPLACE(sql_string, '?', var_tablename);

        PREPARE q FROM @qry;
        EXECUTE q;
        DEALLOCATE PREPARE q;

        FETCH table_cursor INTO var_tablename;
        IF last_row = 1 THEN
            LEAVE lbl_table_cursor;
        END IF;
    END LOOP lbl_table_cursor;

    CLOSE table_cursor;

    END$$

DELIMITER ;

Then you call it with

CALL usp_mysql_foreachtable('SELECT * FROM ? LIMIT 3;');

Upvotes: 1

Related Questions