显示所有表。Describe-like功能

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

如何显示数据库中的所有表，类似于描述myTable的输出。添加功能:

• All tables at once
• 所有的表
• Tablesize
• 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.

来自Rick James的很好的反馈。我不知所措，需要头脑风暴。

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.
• 从概念上说，让整个块被称为“扩展信息”，并为Yay或Nay的生成设置一个开关(参数)。如果'N'那么不要生产它。

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.

我会非常高兴的。自然，信息不会挂在列标题下，我已经在下面的self-answer中显示。因此，一些视觉上的东西，比如缩进，会立刻浮现在我的脑海中，而不是让它成为表格的一部分。粗糙的输出很好。

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.

我想奖励这笔赏金。

1 个解决方案

#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.

这扩展了这里给出的答案。它添加字符集和排序列，并在数据库和表级添加相同的列。包括了在两个选项上的骨架排序:字母排序和表格排序，计算方法与这里类似。我仍然认为这个概念需要一些同行评议。Rolando在DBA这里展示了一个方法，Rick James评论道。它不是一个简单的计算，而且从来没有合并非静态数据，如文本和BLOBs。所以，在计算和分享的时候可以*地改进。无论如何，如果按“大小”，表通常会以所需的排序顺序返回。我不保证它的准确性，因为它涉及到InnoDB文件大小。

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.

会话:这依赖于会话的概念，会话只是调用例程的一个实例。可以将它们看作快照，稍后可以访问它们。此时的数据包含在会话快照中。当您修改表时可能会很有用。特别是,排序。哦，关于排序规则，由于开发人员从internet中剪切和粘贴代码(表级和列级字符集以及排序规则不匹配)的错误排序设置，外键限制经常失败。这就是为什么我把它加入到这个版本中。

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.

例程位于一个数据库Reporting101a中，该数据库包含两个存储过程和一些支持表(几乎都是基于会话的)。大约5表。

Calling Examples:

调用示例:

1. call Reporting101a.describeTables_v3('myDb',@theOutVar,false,true,'size')
2. 调用Reporting101a.describeTables_v3(@theOutVar myDb的,假的,真的,“大小”)
3. call Reporting101a.describeTables_v3('myDb',@theOutVar,false,true,'alpha')
4. 调用Reporting101a.describeTables_v3(@theOutVar myDb的,假的,真的,“α”)
5. call Reporting101a.Print_Tables_Like_Describe(4,'size')
6. 调用Reporting101a.Print_Tables_Like_Describe(4,“大小”)

See NoteA

看到NoteA

Parameters (1st Stored Proc):

参数(1日存储过程):

1. The database name to describe all tables.
2. 描述所有表的数据库名。
3. the INT OUT parameter to hold the session #
4. INT OUT参数用于保存会话#。
5. boolean: do you want the data deleted from the reporting table at the end
6. 布尔值:您是否希望在最后从报表表中删除数据
7. boolean: should we auto-call the Pretty Printing stored procedure that generates describe-like output.
8. 布尔:我们应该自动调用漂亮的打印存储过程来生成类似于描述的输出吗?
9. Sort order: 'size' or 'alpha'. In fact, anything but 'size' will result in 'alpha'.
10. 排序顺序:“大小”或“alpha”。事实上，除了“大小”之外的任何东西都会产生“alpha”。

Parameters (Pretty Printing Stored Proc):

参数(漂亮的打印存储Proc):

1. The session # of prior saved snapshot.
2. 先前保存快照的会话#。
3. Sort order like above.
4. 像上面排序顺序。

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

这些代码都有很好的文档记录，没有将其转换为600行代码，而不是400行代码。

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

例程在Reporting101a数据库中是自包含的和自引用的。所以从任何地方显式地调用它们。

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.

NoteA:对于上面的例子:例1和例2相似，只是排序顺序不同。这是你在一次通话中使用它的常规方式。表的大小总是显示在tablename旁边。只有在“尺寸”的情况下，它才会被分类。第4个参数为True，它在末尾自动调用漂亮的打印存储Proc。否则，将呈现纯简结果集。@theOutVar将被写入，因为它表示创建的会话#。这对于手动调用漂亮的打印存储proc是很有用的，在此后不久，我们就可以使用这些数据，或者在几个月后(冻结的快照)中重新播放结果。因此，例3是这样的情况，您希望根据调用之前的调用检索输出数据，并返回会话#。例如前面提到的示例1和2(其中参数#4是错误的)。或者，如果您只是想要重新报告以前的数据快照。

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

建议的用法是在调用之后不删除会话数据。因此，将参数#3保留为False，表示不删除。

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

例程不会以任何方式影响您的数据。它只修改Reporting101a数据库的数据。

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 Workbench中)，表包装器列将包围为输出生成的所有表。它的列标题为“(空字符串)”。但它仍然相当烦人。这可以在Output1中看到，如下所示。但是，如果您使用命令行开关-N -B(跳过列名和批处理模式)，例如下面的调用:

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.

…它将生成一个非封装的输出。更让人高兴的一点。写入一个sampleOut。txt文件。请参阅下面的Output2。

Tested on: 5.5, 5.6.31, and 5.7.13.

测试时间:5.5、5.6.31和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.

性能:它使用游标进行漂亮的打印。我通常会嘲笑这个概念。但是考虑到这些都是不常见的调用，一两分钟似乎是可以接受的，我很高兴地发现，对于一个有120个表的模式，性能低于10秒。在我的测试中，Linux比Windows快得多。

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
    (   sessionId INT NOT NULL,
        dbName VARCHAR(100) NOT NULL,   -- Tablename
        character_set_name VARCHAR(100) NULL, -- db-level default char set
        collation_name VARCHAR(100) NULL -- db-level default collation
    );

    -- For lack of a better notion, we are calling calls "sessions". The programmer calls the
    -- First Stored Proc, and we call that a session after we get a unique next incrementing number.
    -- That number is the session #. House all output with that as a column value. This allows us to 
    -- move between stored procs, have safe output, have historical snapshots, and retain the data 
    -- via a session # for later use, whatever use.
    INSERT Reporting101a.reportDataSessions(dbName,creationDT) VALUES (pDBName,now());
    SET @mySession=LAST_INSERT_ID(); -- there it is, our session # (read the above paragraph)

    INSERT Reporting101a.reportDataColumns(sessionId,tblName,ordVal,cField,cType,cNull,cKey,cDefault,cExtra,cCharSetName,cCollName,
    colCount,cFieldMaxLen,cTypeMaxLen,cNullMaxLen,cKeyMaxLen,cDefaultMaxLen,cExtraMaxLen,cCharSetNameMaxLen,cCollNameMaxLen)    
    SELECT @mySession,t1.tblName,t1.ordVal,t1.cField,t1.cType,t1.cNull,t1.cKey,t1.cDefault,t1.cExtra,t1.cCharSetName,t1.cCollName,
    t2.colCount,t2.cFieldMaxLen,t2.cTypeMaxLen,t2.cNullMaxLen,t2.cKeyMaxLen,t2.cDefaultMaxLen,t2.cExtraMaxLen,t2.cCharSetNameMaxLen,t2.cCollNameMaxLen
    FROM Reporting101a.tOutput t1
    JOIN Reporting101a.tOutput2 t2
    ON t2.tblName=t1.tblName
    ORDER BY t1.tblName,t1.id;

    INSERT Reporting101a.reportDataTables(sessionId,tblName,character_set_name,collation_name,rowcount,tblSizeMB)
    SELECT DISTINCT @mySession,tblName,NULL,NULL,NULL,NULL
    FROM Reporting101a.reportDataColumns
    WHERE sessionId=@mySession;

    -- http://dev.mysql.com/doc/refman/5.7/en/collation-character-set-applicability-table.html
    -- TLDR; A collation can map to a character set
    UPDATE Reporting101a.reportDataTables rdt
    JOIN INFORMATION_SCHEMA.`TABLES` ist
    ON ist.TABLE_SCHEMA=pDBName AND ist.TABLE_NAME=rdt.tblName
    JOIN INFORMATION_SCHEMA.`COLLATION_CHARACTER_SET_APPLICABILITY` isccsa
    ON isccsa.COLLATION_NAME=ist.TABLE_COLLATION
    SET rdt.character_set_name=isccsa.CHARACTER_SET_NAME,rdt.collation_name=isccsa.COLLATION_NAME,
    rdt.rowcount=ist.TABLE_ROWS,rdt.tblSizeMB=round(((ist.data_length+ist.index_length) / 1048576), 2)
    WHERE rdt.sessionId=@mySession;

    INSERT Reporting101a.reportDataDatabases(sessionId,dbName,character_set_name,collation_name)
    SELECT @mySession,pDBName,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAME
    FROM INFORMATION_SCHEMA.SCHEMATA
    WHERE schema_name = pDBName;

    DROP TEMPORARY TABLE Reporting101a.tOutput;
    DROP TEMPORARY TABLE Reporting101a.tOutput2;
    SET theSession=@mySession; -- the OUT var that came in as a parameter
    SET endDT=NOW();
    UPDATE Reporting101a.reportDataSessions 
    SET partA_BeginDT=beginDT,partA_EndDT=endDT 
    WHERE sessionId=@mySession;
    -- ***************************************************************************
    -- ***************************************************************************
    -- Label "Some_Sort_of_Output":
    IF callTheSecondStoredProc=TRUE THEN
        -- The caller says to call the second stored proc (for Pretty Printing)
        -- This will generate output similar to `DESCRIBE myTable`
        -- But remember, it will do it  for EVERY table in referenced database
        CALL Reporting101a.`Print_Tables_Like_Describe`(@mySession,pOrderBy);
        -- The above call just gave you output.
    ELSE
        -- The caller chose to not auto call the Pretty Printing second stored procedure.
        -- Note, the caller can easily call it right after using the OUT parameter.
        -- So our output will be a resultset of out reportDataColumns table for this session #
        IF pOrderBy!='size' THEN
            -- Order by Alpha for any parameter except 'size'
            SELECT * 
            FROM Reporting101a.reportDataColumns 
            WHERE sessionId=@mySession
            ORDER BY tblName,ordVal;
        ELSE
            -- Order By size DESC
            SELECT rdc.* 
            FROM Reporting101a.reportDataTables rdt
            JOIN Reporting101a.reportDataColumns rdc 
            ON rdc.tblName=rdt.tblName and rdc.sessionId=rdt.sessionId
            WHERE rdt.sessionId=@mySession
            ORDER BY rdt.tblSizeMB DESC,rdc.tblName,rdc.ordVal;
        END IF;
    END IF;
    -- ***************************************************************************
    -- ***************************************************************************

    IF deleteSessionRows=TRUE THEN
        -- The caller says output rows are NOT needed at this point. Delete them.
        -- Note, if this boolean comes in TRUE, you can't call Pretty Printing
        -- second stored procedure with the session # because the data is gone.
        --
        -- Regardless, you are getting something back from "Some_Sort_of_Output" above.
        DELETE FROM Reporting101a.reportDataColumns
        WHERE sessionId=@mySession;

        DELETE FROM Reporting101a.reportDataTables
        WHERE sessionId=@mySession;

        -- Do not delete the row from the Sessions table for now (you can, I'm not atm)
    END IF;
END$$
DELIMITER ;

-- *****************************************************************
-- *****************************************************************
-- *****************************************************************

DROP PROCEDURE IF EXISTS `Reporting101a`.`Print_Tables_Like_Describe`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Reporting101a`.`Print_Tables_Like_Describe`(
    IN pSessionId INT,
    IN pOrderBy CHAR(20) -- 'size' or 'alpha' (see CURSORs below). Size means Tablesize (see ____).
)
BEGIN
    -- Please note: CURSOR stuff must come last in DECLAREs, else "Error 1337: Variable or condition decl aft curs" 
    DECLARE beginDT,EndDT DATETIME; 
    DECLARE done INT DEFAULT FALSE;
    DECLARE curTable VARCHAR(100) DEFAULT '';
    DECLARE bFirst BOOL DEFAULT TRUE;
    DECLARE lv_dbName,CharSetName,CollationName,someClueToCaller VARCHAR(100);
    DECLARE lv_rowCount,lineCount INT;
    DECLARE theSize DECIMAL(14,2);
    DECLARE lv_sessionDT DATETIME;
    DECLARE lv_tblName,lv_cField,lv_cType,lv_cNull,lv_cKey,lv_cDefault,lv_cExtra,lv_cCharSetName,lv_cCollName VARCHAR(100);
    DECLARE lv_ordVal,lv_colCount,lv_cFieldMaxLen,lv_cTypeMaxLen,lv_cNullMaxLen,lv_cKeyMaxLen,
    lv_cDefaultMaxLen,lv_cExtraMaxLen,lv_cCharSetNameMaxLen,lv_cCollNameMaxLen INT;
    -- -------------------------------------------------------------------------------------------------------------------
    -- The below cursor is in Alphabetical Ascending order
    DECLARE curAlpha CURSOR FOR SELECT tblName,ordVal,cField,cType,cNull,cKey,cDefault,cExtra,cCharSetName,cCollName,
    colCount,cFieldMaxLen,cTypeMaxLen,cNullMaxLen,cKeyMaxLen,cDefaultMaxLen,cExtraMaxLen,cCharSetNameMaxLen,cCollNameMaxLen
    FROM Reporting101a.reportDataColumns
    WHERE rdt.sessionId=pSessionId
    ORDER BY tblName,ordVal; -- ascending order by tablename then the ordinal position of each column (1..n)

    -- The below cursor is in Tablesize Descending order, followed by tablename + ordinal position ascending
    DECLARE curSize CURSOR FOR SELECT rdc.tblName,rdc.ordVal,rdc.cField,rdc.cType,rdc.cNull,rdc.cKey,rdc.cDefault,
    rdc.cExtra,rdc.cCharSetName,rdc.cCollName,rdc.colCount,rdc.cFieldMaxLen,rdc.cTypeMaxLen,rdc.cNullMaxLen,
    rdc.cKeyMaxLen,rdc.cDefaultMaxLen,rdc.cExtraMaxLen,rdc.cCharSetNameMaxLen,rdc.cCollNameMaxLen
    FROM Reporting101a.reportDataTables rdt
    JOIN Reporting101a.reportDataColumns rdc
    ON rdc.tblName=rdt.tblName and rdc.sessionId=rdt.sessionId
    WHERE rdt.sessionId=pSessionId
    ORDER BY rdt.tblSizeMB DESC,rdc.tblName,rdc.ordVal; -- tablesize desc, then tablename + ordinal position ascending

    -- What is the Ordinal Position? Simply 1..n as saved in the db 
    -- (see ORDINAL_POSITION in INFORMATION_SCHEMA.COLUMNS)
    --
    -- If it is terribly bothersome, then look into 
    -- "ALTER TABLE" and use FIRST or AFTER   .. :)

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Please note in the above, CURSOR stuff MUST come LAST else "Error 1337: Variable or condition decl aft curs" 
    -- -------------------------------------------------------------------------------------------------------------------
    SET beginDT=NOW();
    SET lineCount=0;
    CREATE TABLE IF NOT EXISTS Reporting101a.reportOutput
    (   lineNum INT AUTO_INCREMENT PRIMARY KEY,
        sessionId INT NOT NULL,
        lineOut varchar(200) NOT NULL
    );

    DELETE FROM Reporting101a.reportOutput WHERE sessionId=pSessionId; -- cleans up a prior run with this session#

    IF pOrderBy!='size' THEN
        OPEN curAlpha; -- we are in using the Alphabetical Cursor (includes typos from caller for the sort column)
    ELSE
        OPEN curSize; -- we are in using the Tablesize Cursor
    END IF;

    -- **Place004** (Top-most output, Session #, then get top-level database info, there is not much of it)
    -- Here is the importance of it though: it documents the state of things at that point in time
    -- And it allows for reporting later by using that Session # (so that is why a Session # should hang out and remain)
    -- So, a snapshot. You don't even need to print and use the output now. You can have it for later. To compare.
    SELECT creationDT INTO lv_sessionDT FROM Reporting101a.reportDataSessions WHERE sessionId=pSessionId;

    SET someClueToCaller='Typo from caller, using Alphabetical';
    IF pOrderBy='size' THEN
        SET someClueToCaller='table size DESC';
    END IF;
    IF pOrderBy='alpha' THEN
        SET someClueToCaller='tablename alphabetical';
    END IF;
    INSERT Reporting101a.reportOutput(sessionId,lineOut)
    SELECT pSessionId,CONCAT('Session: ', pSessionId, ', Date:',lv_sessionDT,' , SortOrder: ',someClueToCaller);

    INSERT Reporting101a.reportOutput(sessionId,lineOut) VALUES (pSessionId,''); -- blank line

    SELECT dbName,character_set_name,collation_name INTO lv_dbName,CharSetName,CollationName
    FROM Reporting101a.reportDataDatabases
    WHERE sessionId=pSessionId;

    INSERT Reporting101a.reportOutput(sessionId,lineOut)
    SELECT pSessionId,CONCAT('Database: ', lv_dbname, ' (CharSet=', COALESCE(CharSetName,''), ', Collation=', COALESCE(CollationName,''), ')');

    INSERT Reporting101a.reportOutput(sessionId,lineOut) VALUES (pSessionId,''); -- blank line
    -- end **Place004** ----------------------------------------------------------------------


    read_loop: LOOP
        IF pOrderBy!='size' THEN
            FETCH curAlpha INTO lv_tblName,lv_ordVal,lv_cField,lv_cType,lv_cNull,lv_cKey,lv_cDefault,lv_cExtra,lv_cCharSetName,
            lv_cCollName,lv_colCount,lv_cFieldMaxLen,lv_cTypeMaxLen,lv_cNullMaxLen,lv_cKeyMaxLen,lv_cDefaultMaxLen,
            lv_cExtraMaxLen,lv_cCharSetNameMaxLen,lv_cCollNameMaxLen ;
        ELSE
            FETCH curSize INTO lv_tblName,lv_ordVal,lv_cField,lv_cType,lv_cNull,lv_cKey,lv_cDefault,lv_cExtra,lv_cCharSetName,
            lv_cCollName,lv_colCount,lv_cFieldMaxLen,lv_cTypeMaxLen,lv_cNullMaxLen,lv_cKeyMaxLen,lv_cDefaultMaxLen,
            lv_cExtraMaxLen,lv_cCharSetNameMaxLen,lv_cCollNameMaxLen ;
        END IF;
        IF done THEN
            LEAVE read_loop;
        END IF;
        IF lv_tblName<>curTable THEN -- **Place006**, "the IF"
            -- We stumbled into a "This is a New table condition (different table)
            -- So we need to create the New Chunk Header Info (also get table-level info like rowcount etc ie **Place7** below)
            -- (as opposed to just displaying the next column info on a table we were already working with)
            IF bFirst=FALSE THEN
                INSERT Reporting101a.reportOutput(sessionId,lineOut)
                SELECT pSessionId,''; -- Insert a blank line between tables (but not the first time)
            ELSE
                SET bFirst=FALSE;
            END IF;

            -- **Place007** (get top-level table info, there is not much of it, just, like, rowcount, charset, collation)
            SELECT rowcount,character_set_name,collation_name,tblSizeMB INTO lv_rowCount,CharSetName,CollationName,theSize
            FROM Reporting101a.reportDataTables
            WHERE sessionId=pSessionId AND tblName=lv_tblName;

            INSERT Reporting101a.reportOutput(sessionId,lineOut)
            SELECT pSessionId,CONCAT(lv_tblName,' (rowcount=',ifnull(lv_rowCount,0),') (Size=',theSize,'MB) (CharSet=',COALESCE(CharSetName,''), ', Collation=',COALESCE(CollationName,''),')');
            -- end **Place007** ----------------------------------------------------------------------


            INSERT Reporting101a.reportOutput(sessionId,lineOut)
            SELECT pSessionId,CONCAT('+-', 
                REPEAT('-',GREATEST(5,lv_cFieldMaxLen)),  '-+-',
                REPEAT('-',GREATEST(4,lv_cTypeMaxLen)), '-+-',
                REPEAT('-',GREATEST(4,lv_cNullMaxLen)), '-+-',
                REPEAT('-',GREATEST(3,lv_cKeyMaxLen)), '-+-',
                REPEAT('-',GREATEST(7,lv_cDefaultMaxLen)), '-+-',
                REPEAT('-',GREATEST(5,lv_cExtraMaxLen)), '-+-',
                REPEAT('-',GREATEST(8,lv_cCharSetNameMaxLen)),'-+-',
                REPEAT('-',GREATEST(5,lv_cCollNameMaxLen)), '-+');

            SET @dashLineNumRow=LAST_INSERT_ID(); -- **Place008**: Save this row id so we can use it again in one sec

            INSERT Reporting101a.reportOutput(sessionId,lineOut)
            SELECT pSessionId,CONCAT('| ', 
                'Field',
                REPEAT(' ',GREATEST(0,lv_cFieldMaxLen-5)),  ' | ',
                'Type',
                REPEAT(' ',GREATEST(0,lv_cTypeMaxLen-4)),   ' | ',
                'Null',
                REPEAT(' ',GREATEST(0,lv_cNullMaxLen-4)),   ' | ',
                'Key',
                REPEAT(' ',GREATEST(0,lv_cKeyMaxLen-3)),    ' | ',
                'Default',
                REPEAT(' ',GREATEST(0,lv_cDefaultMaxLen-7)),    ' | ',
                'Extra',
                REPEAT(' ',GREATEST(0,lv_cExtraMaxLen-5)),  ' | ',
                'Char Set',
                REPEAT(' ',GREATEST(0,lv_cCharSetNameMaxLen-8)),    ' | ',
                'Collation',
                REPEAT(' ',GREATEST(0,lv_cCollNameMaxLen-9)),   ' |');

            INSERT Reporting101a.reportOutput(sessionId,lineOut)
            SELECT pSessionId,lineOut FROM Reporting101a.reportOutput
            WHERE lineNum=@dashLineNumRow; -- related to **Place008** above (just repeat it to close-off header)

            SET curTable=lv_tblName; -- set the variable which is our flag for Next/New/Different table (related: **Place006** above)
        END IF;

        -- The below is the generic insert for a column's info
        INSERT Reporting101a.reportOutput(sessionId,lineOut)
        SELECT pSessionId,
            CONCAT('| ', 
            lv_cField,
            COALESCE(REPEAT(' ',GREATEST(0,lv_cFieldMaxLen-LENGTH(lv_cField))),''),' | ',
            COALESCE(lv_cType,''),
            COALESCE(REPEAT(' ',GREATEST(0,lv_cTypeMaxLen-LENGTH(lv_cType))),''),' | ',
            COALESCE(lv_cNull,''),
            COALESCE(REPEAT(' ',GREATEST(0,lv_cNullMaxLen-LENGTH(lv_cNull))),''),' | ',
            COALESCE(lv_cKey,'   '),
            COALESCE(REPEAT(' ',GREATEST(0,lv_cKeyMaxLen-LENGTH(lv_cKey))),''),' | ',
            COALESCE(lv_cDefault,'       '),
            COALESCE(REPEAT(' ',GREATEST(0,lv_cDefaultMaxLen-LENGTH(lv_cDefault))),''),' | ',
            COALESCE(lv_cExtra,'     '),
            COALESCE(REPEAT(' ',GREATEST(0,lv_cExtraMaxLen-LENGTH(lv_cExtra))),''),' | ',
            lv_cCharSetName, 
            REPEAT(' ',GREATEST(0,lv_cCharSetNameMaxLen-LENGTH(lv_cCharSetName))),' | ',
            lv_cCollName,
            REPEAT(' ',GREATEST(0,lv_cCollNameMaxLen-LENGTH(lv_cCollName))),' |');
        SET lineCount=lineCount+1; -- increment only for column rows not separator rows
        INSERT Reporting101a.reportOutput(sessionId,lineOut)
        SELECT pSessionId,lineOut
        FROM Reporting101a.reportOutput
        WHERE lineNum=@dashLineNumRow;
    END LOOP;
    IF pOrderBy!='size' THEN
        CLOSE curAlpha;
    ELSE 
        CLOSE curSize;
    END IF;

    SET endDT=NOW();

    UPDATE Reporting101a.reportDataSessions 
    SET partB_BeginDT=beginDT,partB_EndDT=endDT,rowCount=lineCount
    WHERE sessionId=pSessionId;

    SELECT lineOut AS '' from Reporting101a.reportOutput WHERE sessionId=pSessionId ORDER BY lineNum;
    -- Note: The whole outer box wrapper is suppressed (which is cool) if we perform a
    --
    -- OSPrompt> mysql -N -B -u UserName -p -e "call Reporting101a.describeTables_v3('*',@theOutVar,false,true,'size')"
    --
    -- That above -N -B suppresses column info (-N), ... (-B) keeps the output left aligned and is Batch mode
    -- I understand (-N), but without (-B) the alignment goes right-aligned
    -- Regardless, it allows us to perform what would appear to 
    -- be merely PRINT statements, if you will. No outer box wrapper in output.
END$$
DELIMITER ;

Output1 (showing wrapping/containing outer table). The two yellow highlighter pieces are the table names.

Output1(显示包装/包含外表)。两个黄色的高亮的部分是表名。

Output2 (Command-line using -N -B switches) which remove the outer wrapping table. See example above in Output section for usage. Effective allows for PRINT like commands in MySQL.

Output2(使用-N -B开关的命令行)，用于删除外部包装表。请参阅输出部分的示例以了解使用情况。有效的支持打印命令在MySQL。