mysql - Display all tables. Describe-like functionality

Question

How can I display all tables in a database similar to the output like DESCRIBE myTable. Adding functionality for:

• All tables at once
• Tablesize
• Character Set and Collation information
• Sort Capabilities

Note: DESCRIBE output is simple, and for a single table at a time.

Edit:

Nice feedback from Rick James. I was at a loss and needed that brainstorm.

If anyone wants to add functionality (to my self-answer) such as an indented row at the bottom of each table for

• Indexes (perhaps 1 line per index showing name and column names separated by a comma
• Cardinality on that Index line above
• Foreign Key Constraints
• Anything else in arms reach your peers might find useful
• Have this whole block called "Extended Info" conceptually, and a switch (a parameter) for Yay or Nay for producing it. If 'N' then don't produce it.

I would be most pleased. Naturally that information would not hang under the column headers already shown in the self-answer below by me. So some visual like an indentation is what immediately comes to mind, not having it exactly part of the table. Rough output is fine.

Consider the following as rough notes that may be of assistance:

create schema x99;
use x99;

create table parent
(   -- assume your have only one parent, ok bad example, it's early
    id int auto_increment primary key,
    fullName varchar(100) not null
)ENGINE=InnoDB;

-- drop table child;
create table child
(   id int auto_increment primary key,
    fullName varchar(100) not null,
    myParent int not null,
    CONSTRAINT `mommy_daddy` FOREIGN KEY (myParent) REFERENCES parent(id)
        ON DELETE CASCADE ON UPDATE CASCADE     
)ENGINE=InnoDB;

create table t3
(   id INT AUTO_INCREMENT PRIMARY KEY,
    myD DATE NOT NULL,
    myI INT NOT NULL,
    KEY `t3_001` (myD,myI)
);

create table t4
(   someCode CHAR(4) PRIMARY KEY,
    codeDescr VARCHAR(500) NOT NULL
);

create table t5
(   id INT AUTO_INCREMENT PRIMARY KEY,
    theCode CHAR(4) NOT NULL,
    d1 DATE NOT NULL,
    i1 INT NOT NULL,
    someOther DATETIME NOT NULL,
    FOREIGN KEY `cd_2_t4` (theCode) REFERENCES t4(someCode),
    FOREIGN KEY `cd_2_t3` (d1,i1) REFERENCES t3(myD,myI)
);

-- The below 2 lines are merely to show cardinality which I am sure is
-- read from INFO SCHEMA too
show indexes in child; -- to pick up cardinality (or from INFO SCHEMA)
show indexes in t5; -- ditto
-- So, I am not suggesting to actually call "show indexes"


-- http://dev.mysql.com/doc/refman/5.7/en/key-column-usage-table.html
-- James Goatcher
SELECT CONCAT( table_name, '.', 
column_name, ' -> ', 
referenced_table_name, '.', 
referenced_column_name ) AS list_of_fks 
FROM information_schema.KEY_COLUMN_USAGE 
WHERE REFERENCED_TABLE_SCHEMA = 'x99' 
AND REFERENCED_TABLE_NAME is not null 
ORDER BY TABLE_NAME, COLUMN_NAME; 
+-----------------------------+
| list_of_fks                 |
+-----------------------------+
| child.myParent -> parent.id |
| t5.d1 -> t3.myD             |
| t5.i1 -> t3.myI             |
| t5.theCode -> t4.someCode   |
+-----------------------------+

Despite the output suggested by James Goatcher on that Webpage, 
perhaps what would look better under table t5 as 2 lines:

t5.d1,i1 -> t3.myD,myI              <----- That there would be swell
t5.theCode -> t4.someCode

-- You may make the assumption that all tables are in the same schema
-- If they aren't and it blows up that is fine

drop schema x99;

I would like to award this bounty.

Answer 1

This expands upon an answer given Here. It adds the Character Set and Collation columns, and the same at the Database and Table-level. Included is barebones sorting on two choices: Alphabetical, and by tablesize, as calculated similar to here. I still think that concept needs some peer review. Rolando on DBA here was showing a method, and Rick James commented. It is not a simple calculation, and never incorporates non-static data such as TEXT and BLOBs. So feel free to improve upon that calculation and share it. Regardless, the tables will generally return in the desired sort order if by 'size'. I make no warranty to its accuracy whatsoever as it relates to InnoDB file size.

It allows for a stub for you to improve sort capabilities. For instance, by having another table for sort order joins based on Main Tables vs Supporting and Code Tables.

Sessions: This relies on the concept of a Session, which is just an instance of you calling the routines. Think of them as Snapshots that can be access later. The data at that point in time is contained in a Session snapshot. Might be useful as you alter tables. In particular, collation. Oh, and about collation, Foreign Key constraints often fail due to incorrect collation setups as developers cut and paste code from the internet (table-level and column-level character set and collation mismatches). That is why I threw that into this version.

The routines live in a database Reporting101a that houses the two stored procedures and some supporting tables (pretty much all session-based). About 5 tables.

Calling Examples:

1. call Reporting101a.describeTables_v3('myDb',@theOutVar,false,true,'size')
2. call Reporting101a.describeTables_v3('myDb',@theOutVar,false,true,'alpha')
3. call Reporting101a.Print_Tables_Like_Describe(4,'size')

See NoteA

Parameters (1st Stored Proc):

1. The database name to describe all tables.
2. the INT OUT parameter to hold the session #
3. boolean: do you want the data deleted from the reporting table at the end
4. boolean: should we auto-call the Pretty Printing stored procedure that generates describe-like output.
5. Sort order: 'size' or 'alpha'. In fact, anything but 'size' will result in 'alpha'.

Parameters (Pretty Printing Stored Proc):

1. The session # of prior saved snapshot.
2. Sort order like above.

The code is fairly well documented, short of turning it into 600 lines of code versus 400.

The routines are self-contained and self-referencing in the Reporting101a database. So call them explicitly from anywhere.

NoteA: As for the examples above: Ex. 1 and 2 as similar, just a different sort order. These are your normal way of using it with just one call. The table size is always displays next to the tablename. Only with 'size' is it sorted descending on it. With the 4th parameter as True, it auto-calls the Pretty Printing Stored Proc at the end. Otherwise, a plain-Jane resultset is rendered. @theOutVar will be written to as it represents the Session # created. That is useful for manually calling the Pretty Printing stored proc shortly thereafter, wedging one of your routines in and using the data, or replaying the results months later (of the frozen snapshot). So, Ex. 3 is the case where you wish to retrieve data for output based on a call prior, having been fed back a Session #. Such as to the before mentioned examples 1 and 2 (wherein parameter #4 would have been False, however). Or, if you simply want to re-report on a prior data snapshot.

The suggested usage is to not delete the Session data after the calls. As such, leave parameter #3 as False, meaning do not delete.

The routines do not affect your data in any way. It only modifies datain the Reporting101a database.

Output: If the routine is run outside of command-line (such as in MySQL Workbench), a table wrapper column will surround the entirety of all of the tables generated for output. It has a column heading of '' (blank string). But it is still rather annoying. This is seen in Output1 shown below. However, if you the leverage command-line switches -N -B (skip column names and batch mode) such as with the following call:

mysql -uYourDBUser -p -N -B -e "call Reporting101a.describeTables_v3('Sample011',@theOutVar,false,true,'size')" > sampleOut.txt

... it will generate a non wrapped output. More pleasing. Written to a sampleOut.txt file. See Output2 below.

Tested on: 5.5, 5.6.31, and 5.7.13.

Performance: It uses CURSORS for the Pretty Printing. I normally laugh at the concept. But considering that these are infrequent calls and a minute or two would seem acceptable perhaps, I was pleased to find the performance to be under 10 seconds for a schema with 120 tables. Linux is much faster than Windows in my testing.

Two Stored Procedures (including the CREATE SCHEMA at top):

CREATE SCHEMA IF NOT EXISTS `Reporting101a`;    -- See **Note1**

DROP PROCEDURE IF EXISTS `Reporting101a`.`describeTables_v3`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Reporting101a`.`describeTables_v3`(
    IN pDBName varchar(100), -- the dbname to report table structures
    OUT theSession int, -- OUT parameter for session# assigned
    IN deleteSessionRows BOOL, -- true for delete rows when done from main reporting table for this session#
    IN callTheSecondStoredProc BOOL, -- TRUE = output is from Pretty output in Second Stored Proc. FALSE= not so pretty output
    IN pOrderBy CHAR(20) -- 'ALPHA' OR 'SIZE'. Alphabetical order, or table size order(desc)
)
BEGIN
    DECLARE thisTable CHAR(100);
    DECLARE beginDT,endDT DATETIME;

    SET beginDT=NOW();
    DROP TEMPORARY TABLE IF EXISTS Reporting101a.tOutput;
    CREATE TEMPORARY TABLE Reporting101a.tOutput
    (   id INT AUTO_INCREMENT PRIMARY KEY,
        tblName VARCHAR(100) NOT NULL,
        ordVal INT NOT NULL,
        cField VARCHAR(100) NOT NULL,
        cType VARCHAR(100) NOT NULL,
        cNull VARCHAR(100) NOT NULL,
        cKey VARCHAR(100) NOT NULL,
        cDefault VARCHAR(100) NULL,
        cExtra VARCHAR(100) NULL,
        cCharSetName VARCHAR(100) NULL,
        cCollName VARCHAR(100) NULL
    );
    DROP TEMPORARY TABLE IF EXISTS Reporting101a.tOutput2;
    CREATE TEMPORARY TABLE Reporting101a.tOutput2
    (   tblName varchar(100) primary key,
        colCount INT NOT NULL,
        cFieldMaxLen INT NOT NULL,
        cTypeMaxLen INT NOT NULL,
        cNullMaxLen INT NOT NULL,
        cKeyMaxLen INT NOT NULL,
        cDefaultMaxLen INT NOT NULL,
        cExtraMaxLen INT NOT NULL,
        cCharSetNameMaxLen INT NOT NULL,
        cCollNameMaxLen INT NOT NULL
    );

    INSERT Reporting101a.tOutput(tblName,ordVal,cField,cType,cNull,cKey,cDefault,cExtra,cCharSetName,cCollName)
    SELECT TABLE_NAME,ORDINAL_POSITION,COLUMN_NAME,COLUMN_TYPE,RPAD(IS_NULLABLE,4,' '), 
    RPAD(COLUMN_KEY,3,' '),RPAD(COLUMN_DEFAULT,7,' '),EXTRA,CHARACTER_SET_NAME,COLLATION_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_schema = pDBName ORDER BY table_name,ordinal_position; 

    UPDATE Reporting101a.tOutput
    SET cExtra='     '
    WHERE cExtra='';

    UPDATE Reporting101a.tOutput
    SET cField=RPAD(cField,5,' ')
    WHERE LENGTH(cField)<5;

    UPDATE Reporting101a.tOutput
    SET cCharSetName=RPAD(COALESCE(cCharSetName,''),8,' ')
    WHERE LENGTH(COALESCE(cCharSetName,''))<8;

    UPDATE Reporting101a.tOutput
    SET cCollName=RPAD(COALESCE(cCollName,''),9,' ')
    WHERE LENGTH(COALESCE(cCollName,''))<9;

    INSERT Reporting101a.tOutput2(tblName,colCount,cFieldMaxLen,cTypeMaxLen,cNullMaxLen,
    cKeyMaxLen,cDefaultMaxLen,cExtraMaxLen,cCharSetNameMaxLen,cCollNameMaxLen) 
    SELECT tblName,COUNT(*),0,0,0,0,0,0,0,0
    FROM Reporting101a.tOutput 
    GROUP BY tblName;

    UPDATE tOutput2 t2
    JOIN
    (   SELECT tblName,MAX(LENGTH(cField)) AS mField,MAX(LENGTH(cType)) AS mType,MAX(LENGTH(cNull)) AS mNull,
        IFNULL(MAX(LENGTH(cKey)),0) AS mKey,IFNULL(MAX(LENGTH(cDefault)),0) AS mDefault,IFNULL(MAX(LENGTH(cExtra)),0) AS mExtra,
        IFNULL(MAX(LENGTH(cCharSetName)),0) AS mCharSetName,IFNULL(MAX(LENGTH(cCollName)),0) AS mCollName
        FROM Reporting101a.tOutput
        GROUP BY tblName
    ) x
    ON x.tblName=t2.tblName
    SET t2.cFieldMaxLen=x.mField,t2.cTypeMaxLen=x.mType,cNullMaxLen=x.mNull,cKeyMaxLen=x.mKey,
    cDefaultMaxLen=x.mDefault,cExtraMaxLen=x.mExtra,cCharSetNameMaxLen=x.mCharSetName,cCollNameMaxLen=x.mCollName;

    CREATE TABLE IF NOT EXISTS Reporting101a.reportDataSessions
    (   -- For the purpose of safe session auto_inc usage, timings, and rowcount
        -- Please don't delete unless you want the sessions to experience aberrant behavior.
        -- That is, the inability to report on prior sessions run. Which is no big deal.
        sessionId INT AUTO_INCREMENT PRIMARY KEY,
        dbName VARCHAR(100) NOT NULL,
        -- character_set_name VARCHAR(100) NULL,
        -- collation_name VARCHAR(100) NULL,
        creationDT DATETIME NOT NULL,
        partA_BeginDT DATETIME NULL,
        partA_EndDT DATETIME NULL,   -- See the following for fractional seconds:
        partB_BeginDT DATETIME NULL, -- http://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html
        partB_EndDT DATETIME NULL,
        rowCount INT NULL
    );

    CREATE TABLE IF NOT EXISTS Reporting101a.reportDataColumns
    (   sessionId INT NOT NULL,
        tblName VARCHAR(100) NOT NULL,  -- Tablename
        ordVal INT NOT NULL,    -- the "position number" of the Column
        cField VARCHAR(100) NOT NULL,   -- The Column
        cType VARCHAR(100) NOT NULL,    -- Datatype
        cNull VARCHAR(100) NOT NULL,    -- Nullability
        cKey VARCHAR(100) NOT NULL, -- Key info
        cDefault VARCHAR(100) NULL, -- Default value
        cExtra VARCHAR(100) NULL,   -- Extra output
        cCharSetName VARCHAR(100) NULL, -- Default value
        cCollName VARCHAR(100) NULL,    -- Extra output
        colCount INT NOT NULL,  -- the columns here and below are de-normalize data
        cFieldMaxLen INT NOT NULL,
        cTypeMaxLen INT NOT NULL,
        cNullMaxLen INT NOT NULL,
        cKeyMaxLen INT NOT NULL,
        cDefaultMaxLen INT NOT NULL,
        cExtraMaxLen INT NOT NULL,
        cCharSetNameMaxLen INT NOT NULL,
        cCollNameMaxLen INT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS Reporting101a.reportDataTables
    (   sessionId INT NOT NULL,
        tblName VARCHAR(100) NOT NULL,  -- tablename
        character_set_name VARCHAR(100) NULL, -- table-level default char set
        collation_name VARCHAR(100) NULL, -- table-level default collation
        rowcount BIGINT NULL, -- rowcount (subject to system refresh, ditto, next column)
        tblSizeMB DECIMAL(14,2) NULL -- in MB
    );

    CREATE TABLE IF NOT EXISTS Reporting101a.reportDataDatabases
    (