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

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

---

Clickhouse 元数据明细

---

SELECTt1.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 元数据明细

---

SELECTtab.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 元数据明细

---

SELECTtab.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
BEGINDECLARE string_c VARCHAR(20000);DECLARE nub VARCHAR(1);DECLARE i INT;SET i =1;SET string_c ='';IF string_a REGEXP patternTHEN  loop_label :LOOPIF i > CHAR_LENGTH(string_a)THEN  LEAVE loop_label;END IF;SET nub = SUBSTRING(string_a,i,1);IF NOT nub REGEXP patternTHEN  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;