查询数据库中所有表以及表结构

查询数据库中所有表以及表结构

贴两个两段代码和截图吧，用的人自己拿去改（MYSQL）

SQL片段1：（这个只是查出来相应的表结构）

在这里插入代码片
SELECT
        TABLE_NAME AS 'tableName',
        COLUMN_NAME AS 'columnName',
        COLUMN_COMMENT AS 'columnComment',
        IS_NULLABLE AS 'nullable',
        DATA_TYPE AS 'dataType',
        CHARACTER_MAXIMUM_LENGTH AS 'strLength',
        NUMERIC_PRECISION AS 'numLength',
        NUMERIC_SCALE AS 'numBit'
FROM
        information_schema.`COLUMNS`
WHERE
        TABLE_SCHEMA = 'test2' -- dm 是数据库名称，需替换
AND TABLE_NAME = 'test' -- base_auth_resource 是表名，需替换
ORDER BY
        TABLE_NAME,
        ORDINAL_POSITION;

SQL片段2：（这个查出所有表，以及每个表的结构）

SELECT 
  table_name AS tableName,
  GROUP_CONCAT(column_name) AS columnName,
  GROUP_CONCAT(column_type) AS columnType,
	GROUP_CONCAT(IS_NULLABLE) AS nullable,
	GROUP_CONCAT(IF(CHARACTER_MAXIMUM_LENGTH="","无",CHARACTER_MAXIMUM_LENGTH)) AS strLength,
	GROUP_CONCAT(IF(NUMERIC_PRECISION="","无",NUMERIC_PRECISION)) AS numLength,
	GROUP_CONCAT(NUMERIC_SCALE) AS numBit,
	GROUP_CONCAT(IF(column_key='PRI','TRUE','FALSE')) AS '主键唯一',
  GROUP_CONCAT(IF(column_comment="","无",column_comment)) AS columnComment
FROM
  information_schema.`COLUMNS`
WHERE table_schema = 'test2'
GROUP BY tableName
ORDER BY tableName;

---

看别人整理的，真的是是乱。自己整理，你们自食其用