AUTHOR: CenLiang
DATE: 2020/04/24
REV: 2.0
Oracle、MySQL、PostgreSQL、DB2、Hive 数据字典
1、Oracle 数据字典
## 1.1-oracle-表
SELECT b.username,
a.table_name,
a.tablespace_name,
a.num_rows,
a.status,
a.logging
dba_tables a,
(SELECT username, user_id, account_status, created
dba_users
WHERE account_status
username , SYSTEM, SYSMAN, DBSNMP, CTXSYSORDER account_status WHERE a.owner b.username
a.tablespace_name
a.num_rows
ORDER a.owner, a.tablespace_name, a.table_name;
## 1.2-oracle-分区表
SELECT b.username,
a.table_name,
a.tablespace_name,
a.num_rows,
a.partition_name,
a.partition_position
dba_tab_partitions a,
(SELECT username, user_id, account_status, created
dba_users
WHERE account_status
username , SYSTEM, SYSMAN, DBSNMP, CTXSYSORDER account_status WHERE a.table_owner b.username
ORDER b.username, a.table_name,a.partition_position;
## 1.3-oracle-字段
SELECT b.username,
a.table_name,
a.column_id,
a.column_name,
a.data_type,
a.data_length,
a.nullable
dba_tab_columns a,
(SELECT username, user_id, account_status, created
dba_users
WHERE account_status
username , SYSTEM, SYSMAN, DBSNMP, CTXSYSORDER account_status WHERE a.owner b.username
ORDER b.username, a.table_name, a.column_id;
2、MySQL 数据字典
## 2.1-mysql-表
SELECT
TABLE_SCHEMA AS '库名',
TABLE_NAME AS '表名',
TABLE_COMMENT AS '表说明',
TABLE_ROWS AS '数据量',
CREATE_TIME AS '创建日期'
FROM
information_schema.`TABLES`
WHERE
TABLE_SCHEMA NOT IN (
'mysql',
'information_schema',
'performance_schema',
'sys'
)
ORDER BY
TABLE_SCHEMA,
TABLE_NAME;
## 2.2-mysql-字段
SELECT
TABLE_SCHEMA AS '库名',
TABLE_NAME AS '表名',
a.TABLE_COMMENT AS '表说明',
b.ORDINAL_POSITION AS '列ID',
b.COLUMN_NAME AS '字段名',
b.COLUMN_TYPE AS '数据类型',
b.IS_NULLABLE AS '允许为空',
b.COLUMN_KEY AS '主键',
b.COLUMN_DEFAULT AS '默认值',
b.EXTRA AS '其他',
b.COLUMN_COMMENT AS '字段说明'
FROM
information_schema.`TABLES` a
JOIN information_schema.`COLUMNS` b USING (TABLE_SCHEMA,TABLE_NAME)
WHERE
a.TABLE_SCHEMA NOT IN (
'mysql',
'information_schema',
'performance_schema',
'sys'
)
GROUP BY
TABLE_SCHEMA,
TABLE_NAME,
b.ORDINAL_POSITION
ORDER BY
TABLE_SCHEMA,
TABLE_NAME,
b.ORDINAL_POSITION;
3、PostgreSQL 数据字典
## 3.1-pg-表
SELECT
pt.tableowner AS "用户名",
it.table_catalog AS "库名",
it.table_schema AS "模式名",
it."table_name" AS "表名",
-- nsp.oid as nspoid,
-- pc.oid as pcoid,
-- pd.objoid,
-- pd.objsubid,
pd.description AS "表说明"
FROM
information_schema.tables it
LEFT JOIN pg_catalog.pg_tables pt ON ( pt.schemaname = it.table_schema AND pt.tablename = it."table_name" )
LEFT JOIN pg_catalog.pg_namespace nsp ON (pt.schemaname = nsp.nspname)
JOIN pg_catalog.pg_class pc ON ( nsp.oid = pc.relnamespace AND pt.tablename = pc.relname )
LEFT JOIN pg_catalog.pg_description pd ON ( pc.oid = pd.objoid AND pd.objsubid = 0 )
WHERE
it.table_schema NOT IN ( 'information_schema', 'pg_catalog', 'pg_toast' )
ORDER BY
it.table_schema,
it."table_name";
## 3.2-pg-字段
SELECT
pt.tableowner AS "用户名",
ic.table_catalog AS "库名",
-- nsp.oid AS nspoid,
ic.table_schema AS "模式名",
-- pc.oid AS pcoid,
-- pa.attrelid AS paattrelid,
-- pd.objoid,
ic."table_name" AS "表名",
pd_tab.description AS "表说明" ,
-- pa.attnum,
-- pd.objsubid,
ic.ordinal_position AS "列ID",
ic."column_name" AS "字段名",
ic.udt_name AS "数据类型",
ic.is_nullable AS "允许为空",
ic.column_default AS "默认值",
pd_col.description AS "字段说明"
FROM
information_schema."columns" ic
-- LEFT JOIN information_schema.tables it ON ( ic.table_schema = it.table_schema AND ic."table_name" = it."table_name" )
LEFT JOIN pg_catalog.pg_tables pt ON ( ic.table_schema = pt.schemaname AND ic."table_name" = pt.tablename )
LEFT JOIN pg_catalog.pg_namespace nsp ON ( ic.table_schema = nsp.nspname )
JOIN pg_catalog.pg_class pc ON ( nsp.oid = pc.relnamespace AND ic."table_name" = pc.relname )
LEFT JOIN pg_catalog.pg_description pd_tab ON ( pc.oid = pd_tab.objoid AND pd_tab.objsubid = 0 )
LEFT JOIN pg_catalog.pg_attribute pa ON ( pc.oid = pa.attrelid AND ic.ordinal_position = pa.attnum )
LEFT JOIN pg_catalog.pg_description pd_col ON ( pa.attrelid = pd_col.objoid AND pa.attnum = pd_col.objsubid AND pd_col.objsubid > 0 )
WHERE
ic.table_schema NOT IN ( 'information_schema', 'pg_catalog', 'pg_toast' )
ORDER BY
ic.table_schema,
ic."table_name",
ic.ordinal_position;
4、DB2 数据字典
## 4.1-db2-模式
SELECT
(
SELECT DISTINCT
(table_catalog)
FROM
sysibm.tables WITH ur) AS TABLE_CATALOG,
SCHEMATA.SCHEMANAME AS schema_name,
SCHEMATA.CREATE_TIME
FROM
syscat.SCHEMATA
WHERE
SCHEMATA.SCHEMANAME NOT IN ('SYSIBM',
'SYSCAT',
'SYSFUN',
'SYSSTAT',
'SYSPROC',
'SYSIBMADM',
'SYSIBMINTERNAL',
'SQLJ',
'SYSTOOLS',
'NULLID',
'SYSIBMTS',
'SYSPUBLIC');
## 4.2-db2-表
SELECT
it.TABLE_CATALOG,
ct.TABSCHEMA AS schema_name,
ct.TABNAME AS table_name,
ct.REMARKS AS table_comment,
ct.CREATE_TIME,
ct.LASTUSED,
ct.COLCOUNT AS column_num,
ct.CARD AS table_card
FROM
syscat.TABLES ct
JOIN
SYSIBM.TABLES it
ON
ct.TABSCHEMA=it.TABLE_SCHEMA
AND ct.TABNAME=it.TABLE_NAME
WHERE
ct.TABSCHEMA NOT IN ('SYSIBM',
'SYSCAT',
'SYSFUN',
'SYSSTAT',
'SYSPROC',
'SYSIBMADM',
'SQLJ',
'SYSTOOLS',
'NULLID',
'SYSIBMINTERNAL',
'SYSIBMTS',
'SYSPUBLIC')
ORDER BY
ct.TABSCHEMA,
ct.TABNAME;
## 4.3-db2-字段
SELECT
ic.TABLE_CATALOG,
cc.TABSCHEMA AS schema_name,
cc.TABNAME AS table_name,
ct.REMARKS AS table_comment,
ic.ORDINAL_POSITION,
cc.COLNAME AS column_name,
cc.TYPENAME AS data_type,
cc.LENGTH,
ic.IS_NULLABLE,
cc.DEFAULT,
cc.REMARKS AS column_comment,
cc.HIGH2KEY,
cc.LOW2KEY,
cc.COLCARD AS column_card
FROM
syscat.TABLES ct,
syscat.COLUMNS cc,
SYSIBM.COLUMNS ic
WHERE
ct.TABSCHEMA=cc.TABSCHEMA
AND ct.TABNAME=cc.TABNAME
AND cc.TABSCHEMA=ic.TABLE_SCHEMA
AND cc.TABNAME=ic.TABLE_NAME
AND cc.COLNAME =ic.COLUMN_NAME
AND cc.TABSCHEMA NOT IN ('SYSIBM',
'SYSCAT',
'SYSFUN',
'SYSSTAT',
'SYSPROC',
'SYSIBMADM',
'SQLJ',
'SYSTOOLS',
'NULLID',
'SYSIBMINTERNAL',
'SYSIBMTS',
'SYSPUBLIC')
ORDER BY
cc.TABSCHEMA,
cc.TABNAME,
ic.ORDINAL_POSITION;
5、Hive 数据字典
## 5.1-hive-表
SELECT
d.OWNER_NAME AS '用户名',
-- DB_ID,
d.`NAME` AS '库名',
-- t.TBL_ID,
-- t.SD_ID,
t.TBL_NAME AS '表名',
t1.`comment` AS '表说明',
t1.numRows AS '数据量',
FROM_UNIXTIME(t.CREATE_TIME) AS '创建时间'
FROM
hive.DBS d JOIN hive.TBLS t USING(DB_ID)
JOIN (SELECT
TBL_ID,
MAX(CASE tp.PARAM_KEY WHEN 'comment' THEN tp.PARAM_VALUE ELSE NULL END) AS `comment`,
MAX(CASE tp.PARAM_KEY WHEN 'numRows' THEN tp.PARAM_VALUE ELSE NULL END) AS `numRows`
FROM hive.TABLE_PARAMS tp
GROUP BY TBL_ID ) t1 USING(TBL_ID)
ORDER BY d.`NAME`,t.TBL_NAME;
## 5.2-hive-字段
SELECT
d.OWNER_NAME AS '用户名',
-- DB_ID,
d.`NAME` AS '库名',
-- t.TBL_ID,
-- t.SD_ID,
t.TBL_NAME AS '表名',
t1.`comment` AS '表说明',
c1.INTEGER_IDX AS '列ID',
c1.COLUMN_NAME AS '字段名',
c1.TYPE_NAME AS '数据类型',
c1.`COMMENT` AS '字段说明'
FROM
hive.DBS d JOIN hive.TBLS t USING(DB_ID)
JOIN (SELECT
TBL_ID,
MAX(CASE tp.PARAM_KEY WHEN 'comment' THEN tp.PARAM_VALUE ELSE NULL END) AS `comment`
FROM hive.TABLE_PARAMS tp
GROUP BY TBL_ID ) t1 USING(TBL_ID)
JOIN (SELECT
s.SD_ID,
CD_ID,
c.INTEGER_IDX,
c.COLUMN_NAME,
c.TYPE_NAME,
c.`COMMENT`
FROM
hive.SDS s
JOIN hive.COLUMNS_V2 c USING (CD_ID)) c1 USING(SD_ID)
ORDER BY d.`NAME`,t.TBL_NAME,c1.INTEGER_IDX;