【数据库表及字段统计SQL】【mysql】【clickhouse】【oracle】

最近在一些元数据整理时，需要对数据库进行澄清，奈何数据库没有专门的运维工程师，得自行取相关信息，故最终整理了相关统计语句。

Clickhouse 元数据明细

SELECT
     t1.database  AS `库名`
    ,t1.name      AS `表名`
    ,replaceRegexpAll(toString(t1.comment),'\n|\\|','') AS `表注释`
    ,t2.name AS `字段名`
    ,replaceRegexpAll(toString(t2.comment),'\n|\\|','') AS `字段注释`
    ,t2.position  AS `字段序号`
FROM  system.tables t1
LEFT JOIN
system.columns t2
ON t1.database = t2.database AND t1.name = t2.table
WHERE  t1.database IN ('test')
ORDER BY `库名`
    ,`表名`
    ,`字段序号`

replaceRegexpAll和toString由于是clickhouse数据库，严格区分大小写。

Oracle 元数据明细

SELECT
     tab.owner                                                              库名
    ,tab.table_name                                                        表名
    ,REGEXP_REPLACE(REGEXP_REPLACE(tab.comments,chr(10),'；'),'\|','；')   表注释
    ,col.column_name                                                        字段名
    ,REGEXP_REPLACE(REGEXP_REPLACE(colc.comments,chr(10),'；'),'\|','；')  字段注释
    ,col.column_id                                                         字段序号
FROM all_tab_comments tab
LEFT JOIN all_tab_columns col
ON tab.owner=col.owner AND tab.table_name=col.table_name
LEFT JOIN all_col_comments colc
ON tab.owner=colc.owner AND tab.table_name=colc.table_name AND col.column_name =colc.column_name
WHERE tab.owner IN ('test')
ORDER BY tab.owner
	,tab.table_name
	,tab.comments
	,col.column_id

MYSQL 元数据明细

SELECT
     tab.table_schema                                                  库名
    ,tab.table_name                                                    表名
    ,CONVERT(REGEXP_REPLACE(tab.table_comment,'\n|\\|',';')USING utf8) 表注释
    ,col.column_name                                                   字段名
    ,CONVERT(REGEXP_REPLACE(col.column_comment,'\n|\\|',';')USING utf8) 字段注释
    ,col.ordinal_position                                              字段序号
FROM information_schema.tables tab
LEFT JOIN information_schema.columns  col
ON tab.table_schema = col.table_schema AND tab.table_name = col.table_name
WHERE 1=1
ORDER BY tab.table_schema
	,tab.table_name
	,col.ordinal_position

其中REGEXP_REPLACE正则替换函数mysql5.7及以下需要自定义，5.8及以后数据库自带

#创建前删除已经创建的自定义函数
DROP FUNCTION IF EXISTS test.regexp_replace;
#创建 regexp_replace函数
DELIMITER $$
$$
CREATE FUNCTION  test.`regexp_replace`(string_a VARCHAR(20000),pattern VARCHAR(20000),string_b VARCHAR(20000))  RETURNS VARCHAR(20000)  DETERMINISTIC
BEGIN
    DECLARE string_c VARCHAR(20000);
    DECLARE nub VARCHAR(1);
    DECLARE i INT;
    SET i =1;
    SET string_c ='';
    IF string_a REGEXP pattern
        THEN  loop_label :
            LOOP
                IF i > CHAR_LENGTH(string_a)
                    THEN  LEAVE loop_label;
                END IF;
                SET nub = SUBSTRING(string_a,i,1);
                IF NOT nub REGEXP pattern
                    THEN  SET string_c = CONCAT(string_c,nub);
                    ELSE  SET string_c = CONCAT(string_c,string_b);
                END IF;
                SET i=i+1;
            END LOOP;
        ELSE SET string_c = string_a;
    END IF;
    RETURN string_c;
END$$
DELIMITER;