mysql - Mostrar todas las tablas. Describir la funcionalidad como

Question

¿Cómo puedo mostrar todas las tablas en una base de datos similar a la salida como DESCRIBE myTable ? Añadiendo funcionalidad para:

• Todas las mesas a la vez.
• Tamaño de la mesa
• Conjunto de caracteres e información de colación
• Ordenar capacidades

Nota: la salida DESCRIBE es simple, y para una sola tabla a la vez.

Editar:

Buena respuesta de Rick James. Estaba en una pérdida y necesitaba esa lluvia de ideas.

Si alguien desea agregar funcionalidad (a mi respuesta automática) como una fila con sangría en la parte inferior de cada tabla para

• Índices (quizás 1 línea por índice que muestre nombres de nombres y columnas separados por una coma)
• Cardinalidad en esa línea de índice arriba
• Restricciones de clave externa
• Cualquier otra cosa en brazos que sus compañeros puedan encontrar útil
• Tenga este bloque completo llamado "Información extendida" conceptualmente, y un interruptor (un parámetro) para Yay o Nay para producirlo. Si ''N'' entonces no lo produzcas.

Estaría muy contento. Naturalmente, esa información no se colgaría debajo de los encabezados de las columnas que ya se muestran en la auto-respuesta a continuación. Entonces, lo que viene a la mente inmediatamente es algo visual, como una muesca, no tenerlo exactamente como parte de la tabla. La salida aproximada está bien.

Considere lo siguiente como notas aproximadas que pueden ser de ayuda:

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;

Me gustaría otorgar esta recompensa.

Answer 1

Esto se expande sobre una respuesta dada Here . Agrega las columnas de Conjunto de caracteres y Intercalación, y lo mismo en la Base de datos y en el Nivel de tabla. Se incluye la clasificación de barebones en dos opciones: Alfabético y por tamaño de tabla, como se calcula de manera similar a here . Todavía creo que ese concepto necesita una revisión por pares. Rolando en DBA here estaba mostrando un método, y Rick James comentó. No es un cálculo simple, y nunca incorpora datos no estáticos como TEXT y BLOB s. Así que siéntete libre de mejorar ese cálculo y compartirlo. En cualquier caso, las tablas generalmente se devolverán en el orden de clasificación deseado si por "tamaño". No hago ninguna garantía de su precisión en lo que respecta al tamaño del archivo InnoDB.

Te permite un talón para que puedas mejorar las capacidades de clasificación. Por ejemplo, al tener otra tabla para las combinaciones de orden de clasificación basadas en Tablas principales vs Soporte y Tablas de códigos.

Sesiones: Esto se basa en el concepto de una sesión, que es solo una instancia en la que usted llama a las rutinas. Piense en ellos como instantáneas a las que se puede acceder más tarde. Los datos en ese momento se encuentran en una instantánea de la sesión. Podría ser útil cuando alteres tablas. En particular, la colación. Ah, y sobre la intercalación, las restricciones de clave externa a menudo fallan debido a configuraciones de intercalación incorrectas a medida que los desarrolladores cortan y pegan el código de Internet (conjunto de caracteres a nivel de tabla y de columna y desajustes de intercalación). Es por eso que eché eso en esta versión.

Las rutinas viven en una base de datos Reporting101a que alberga los dos procedimientos almacenados y algunas tablas de soporte (casi todas basadas en sesiones). Alrededor de 5 mesas.

Ejemplos de llamadas:

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'')

Ver nota a

Parámetros (1er proceso almacenado):

1. El nombre de la base de datos para describir todas las tablas.
2. el parámetro INT OUT para mantener la sesión #
3. booleano: ¿desea que los datos se eliminen de la tabla de informes al final?
4. booleano: deberíamos llamar automáticamente al procedimiento almacenado de Impresión Bonita que genera un resultado similar al describe .
5. Orden de clasificación: ''tamaño'' o ''alfa''. De hecho, cualquier cosa que no sea ''tamaño'' resultará en ''alfa''.

Parámetros (Procesamiento Bastante Imprimido):

1. El número de sesión de la instantánea guardada anteriormente.
2. Orden de clasificación como el de arriba.

El código está bastante bien documentado, a excepción de convertirlo en 600 líneas de código en lugar de 400.

Las rutinas son autocontenidas y se hacen referencia a sí mismas en la base de datos Reporting101a . Así que llámalos explícitamente desde cualquier lugar.

NotaA: En cuanto a los ejemplos anteriores: Ej. 1 y 2 como similares, solo un orden diferente. Esta es su forma normal de usarlo con una sola llamada. El tamaño de la tabla siempre se muestra junto al nombre de la tabla. Sólo con ''tamaño'' se clasifica descendiendo en él. Con el 4to parámetro como Verdadero , automáticamente llama al Procesamiento de Impresión Bonita al final. De lo contrario, se representa un conjunto de resultados de Jane simple. @theOutVar se escribirá en como representa el # de sesión creado. Eso es útil para llamar manualmente el proceso almacenado de Pretty Printing poco después, agregar una de sus rutinas y usar los datos, o reproducir los resultados meses después (de la instantánea congelada). Entonces, Ex. 3 es el caso en el que desea recuperar datos para la salida basados ​​en una llamada anterior, después de haber devuelto un número de sesión. Como los ejemplos 1 y 2 mencionados anteriormente (en donde el parámetro # 4 hubiera sido Falso , sin embargo). O, si simplemente desea volver a informar sobre una instantánea de datos anterior.

El uso sugerido es no eliminar los datos de la sesión después de las llamadas. Como tal, deje el parámetro # 3 como Falso , lo que significa no eliminar.

Las rutinas no afectan tus datos de ninguna manera. Solo modifica los datos de la base de datos Reporting101a .

Salida: si la rutina se ejecuta fuera de la línea de comandos (como en MySQL Workbench), una columna de envoltura de tabla rodeará la totalidad de todas las tablas generadas para la salida. Tiene un encabezado de columna de '''' (cadena en blanco). Pero sigue siendo bastante molesto. Esto se ve en la Salida 1 que se muestra a continuación. Sin embargo, si utiliza la línea de comando switches -N -B (omita los nombres de columna y el modo por lotes), por ejemplo, con la siguiente llamada:

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

... generará una salida no envuelta. Más agradable. Escrito en un archivo sampleOut.txt . Ver Output2 a continuación.

Probado en: 5.5 , 5.6.31 y 5.7.13 .

Rendimiento: Utiliza CURSORS para la Impresión Bonita. Normalmente me río del concepto. Pero teniendo en cuenta que estas son llamadas poco frecuentes y que un minuto o dos parecerían aceptables, me complació encontrar que el rendimiento es inferior a 10 segundos para un esquema con 120 tablas. Linux es mucho más rápido que Windows en mis pruebas.

Dos procedimientos almacenados (incluido CREATE SCHEMA en la parte superior):

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(''stackoverflow'',@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 ;

Salida 1 (que muestra el envoltorio / que contiene la tabla exterior). Las dos piezas resaltadoras amarillas son los nombres de las tablas.

Output2 (línea de comando que usa los conmutadores -N -B ) que elimina la tabla de envoltura externa. Vea el ejemplo anterior en la sección de Salida para el uso. Efectivo permite PRINT como comandos en MySQL.