数据字典SQL

使用SQL语句生成数据库字典表数据

1. mysql

SELECT
        table_name AS '表名',
    column_name AS '字段名',
    column_type AS '字段类型',
    ( CASE WHEN is_nullable = 'YES' THEN '是' ELSE '否' END ) AS '是否可空',
    ( CASE WHEN column_key = 'PRI' THEN '是' ELSE '否' END ) AS '是否主键',
    column_comment AS '注释' 
FROM
    information_schema.COLUMNS 
WHERE 
    table_schema = '***db' AND table_name like 'tb_%'
ORDER BY table_name , ordinal_position ;
-- ordinal_position 表示字段在表中实际的排序

只显示表明和注释的话

 SELECT 
    table_name AS '表名', 
    table_comment AS '注释'
FROM 
    information_schema.tables
WHERE 
    table_schema = '***db' 
    AND table_name like 'tb_%'
ORDER BY 
    table_name;

2. postgresql

SELECT a.attnum as 序号, c.relname as 表,a.attname AS 名称, t.typname ||'(' ||coalesce(col.character_maximum_length,a.attlen,NULL)||')' as 类型, 
case a.attnotnull WHEN 't' THEN 'n' ELSE 'y' END AS 可为空,col.column_default as 默认值, b.description AS 注释
FROM pg_class c ,information_schema.columns col , pg_attribute a
LEFT JOIN pg_description b ON a.attrelid = b.objoid  AND a.attnum = b.objsubid, pg_type t
WHERE c.relname like 'zh%'
    AND a.attnum > 0
    AND a.attrelid = c.oid
    AND a.atttypid = t.oid
        AND col.table_name = c.relname 
        AND a.attname = col."column_name"         
ORDER BY c.relname,a.attnum;

3. mysql将数据生成json格式

-- 定义变量
SET @schema = 'jeecg-boot';
SET @table = 'jimu_report_db';
SET @match = 'jimu_report_id=1004527912577679360';

-- 获取表结构信息，动态生成 JSON 
SET @sql = NULL;
SELECT 
    GROUP_CONCAT(CONCAT('''',column_name,'''',',',column_name)) INTO @sql
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_SCHEMA = @schema AND TABLE_NAME = @table
ORDER BY  ordinal_position;
-- 打印生成的 SQL 代码
SELECT @sql;
-- 使用动态 SQL 生成 JSON
SET @sql = CONCAT('SELECT JSON_OBJECT(',@sql,') AS json_data FROM ',@table,' WHERE ',@match,' LIMIT 1;');
-- 打印生成的 SQL 代码
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;