一、数据库驱动类、端口、默认用户名密码
| MySQL | com.mysql.jdbc.Driver | 3306 | root | root |
| DB2 | com.ibm.db2.jcc.DB2Driver | 50000 | db2inst1 | db2inst1 |
| Oracle | oracle.jdbc.OracleDriver | 1521 | system | manager |
| SQLServer | com.microsoft.sqlserver.jdbc.SQLServerDriver | 1433 | sa | sa |
| PostgreSQL | org.postgresql.Driver | 5432 | postgres | postgres |
| Mariadb | org.mariadb.jdbc.Driver | 3306 | root | root |
| GreenPlum | com.pivotal.jdbc.GreenplumDriver | 5432 | gpadmin | gpadmin |
| Teradata | com.teradata.jdbc.TeraDriver | 1025 | dbc | dbc |
二、数据库的JDBC连接字符串
| MySQL | jdbc:mysql://${dbHost}:${dbPort}/${dbName}?useSSL=false&useUnicode=true&characterEncoding=utf8 |
| DB2 | jdbc:db2://${dbHost}:${dbPort}/${dbName} |
| Oracle | jdbc:oracle:thin:@${dbHost}:${dbPort}:${dbName} # SID jdbc:oracle:thin:@//${dbHost}:${dbPort}/${dbName} # ServiceName |
| SQLServer | jdbc:sqlserver://${dbHost}:${dbPort};databaseName=${dbName} |
| PostgreSQL | jdbc:postgresql://${dbHost}:${dbPort}/${dbName} |
| Mariadb | jdbc:mariadb://${dbHost}:${dbPort}/${dbName} |
| GreenPlum | jdbc:pivotal:greenplum://${dbHost}:${dbPort};DatabaseName=${dbName} |
| Teradata | jdbc:teradata://${dbHost}/DATABASE=${dbName},DBS_PORT=${dbPort},CLIENT_CHARSET=EUC_CN,TMODE=TERA,CHARSET=ASCII |
三、数据库的元数据抽取
- MySQL
/* 完成 */
SELECT UPPER(TRIM(T.TABLE_SCHEMA)) AS 模式
, UPPER(TRIM(T.TABLE_NAME)) AS 表名称
, T.TABLE_COMMENT AS 表注释
, UPPER(TRIM(C.COLUMN_NAME)) AS 列名称
, C.COLUMN_COMMENT AS 列注释
, UPPER(TRIM(C.DATA_TYPE)) AS 列类型
, IFNULL(C.CHARACTER_MAXIMUM_LENGTH,C.NUMERIC_PRECISION) AS 列长度
, C.NUMERIC_SCALE AS 小数位数
, CASE WHEN C.COLUMN_KEY = 'PRI' THEN 'Y' ELSE NULL END AS 是否主键
, CASE WHEN C.IS_NULLABLE = 'NO' THEN 'N' ELSE NULL END AS 是否可为空
, C.COLUMN_DEFAULT AS 列默认值
, I.INDEXES AS 索引名称
, C.ORDINAL_POSITION AS 列顺序
FROM INFORMATION_SCHEMA.COLUMNS C -- 列
INNER JOIN INFORMATION_SCHEMA.TABLES T -- 表
ON T.TABLE_NAME = C.TABLE_NAME
AND T.TABLE_SCHEMA = C.TABLE_SCHEMA
AND T.TABLE_TYPE = 'BASE TABLE' -- 限制为表
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K -- 列约束
ON C.TABLE_NAME = K.TABLE_NAME
AND C.COLUMN_NAME = K.COLUMN_NAME
AND C.TABLE_SCHEMA = K.TABLE_SCHEMA
LEFT JOIN ( -- 查询列上的索引名称(多个逗号分隔)
SELECT TABLE_NAME, COLUMN_NAME, GROUP_CONCAT(INDEX_NAME) AS INDEXES
FROM INFORMATION_SCHEMA.STATISTICS
WHERE UPPER(TABLE_SCHEMA) = UPPER('${dbName}')
GROUP BY TABLE_NAME, COLUMN_NAME
) I
ON C.TABLE_NAME = I.TABLE_NAME
AND C.COLUMN_NAME = I.COLUMN_NAME
WHERE UPPER(TRIM(C.TABLE_SCHEMA)) = UPPER('${dbName}')
ORDER BY C.TABLE_NAME, C.ORDINAL_POSITION
- DB2
/* 完成
* SYSIBM 基本表,对DB2使用进行最优化
* SYSCAT 基于SYSIBM表的视图,对平常轻负荷使用进行优化
*/
SELECT TRIM(C.TABSCHEMA) AS 模式
, T.TABNAME AS 表名称
, T.REMARKS AS 表注释
, C.COLNAME AS 列名称
, C.REMARKS AS 列注释
, C.TYPENAME AS 列类型
, C.LENGTH AS 列长度
, C.SCALE AS 小数位数
, NVL2(P.COLNAME,'Y',NULL) AS 主键
, DECODE(C.NULLS,'N','N',NULL) AS 可空
, VARCHAR(C.DEFAULT) AS 默认
, Y.INDEXES AS 索引
, C.COLNO AS 列顺序
FROM SYSCAT.COLUMNS C -- 列
INNER JOIN SYSCAT.TABLES T -- 表
ON C.TABSCHEMA = T.TABSCHEMA
AND C.TABNAME = T.TABNAME
LEFT JOIN ( -- DbVisualizer的DEBUG中取得
SELECT TC.CONSTNAME, KC.COLNAME,TC.TABSCHEMA,TC.TABNAME
FROM SYSCAT.TABCONST TC -- 表约束: 主键P, 外键F, 唯一U, 表检查K
, SYSCAT.KEYCOLUSE KC -- 关键列使用表
WHERE TC.TABSCHEMA = KC.TABSCHEMA
AND TC.TABNAME = KC.TABNAME
AND TC.CONSTNAME = KC.CONSTNAME
AND TC.TYPE = 'P' -- 限制为主键
) P
ON C.TABSCHEMA = P.TABSCHEMA
AND C.TABNAME = P.TABNAME
AND C.COLNAME = P.COLNAME
LEFT JOIN ( -- 列及列上的多个索引
SELECT X.TABSCHEMA, X.TABNAME, X.COLNAME, LISTAGG(X.INDNAME, ',') WITHIN GROUP(ORDER BY COLSORT) AS INDEXES
FROM (-- 索引与列的对应关系
SELECT C.TABSCHEMA, C.TABNAME, C.COLNAME, I.INDNAME, INSTR(I.COLNAMES, '+' || C.COLNAME) AS COLSORT
FROM SYSCAT.INDEXES I -- 索引
INNER JOIN SYSCAT.COLUMNS C -- 列
ON C.TABSCHEMA = I.TABSCHEMA
AND C.TABNAME = I.TABNAME
AND INSTR(I.COLNAMES, '+' || C.COLNAME) > 0 -- DB2索引对应的列放在COLNAMES里面,格式: +列1+列2
WHERE I.OWNERTYPE = 'U'
) X
GROUP BY X.TABSCHEMA, X.TABNAME, X.COLNAME
) Y
ON C.TABSCHEMA = Y.TABSCHEMA
AND C.TABNAME = Y.TABNAME
AND C.COLNAME = Y.COLNAME
WHERE T.TYPE = 'T'
AND T.OWNERTYPE = 'U'
AND C.TABSCHEMA NOT LIKE 'SYS%'
AND C.TABSCHEMA <> 'SQLJ'
ORDER BY C.TABSCHEMA, C.TABNAME, C.COLNO
- Oracle
/* 完成 */
SELECT
SYS_CONTEXT('USERENV','CURRENT_SCHEMA') AS 模式
, H.TABLE_NAME AS 表名称
, H.COMMENTS AS 表注释
, T.COLUMN_NAME AS 列名称
, A.COMMENTS AS 列注释
, T.DATA_TYPE AS 列类型
, NVL(T.DATA_LENGTH, T.DATA_PRECISION) AS 列长度
, T.DATA_SCALE AS 小数位数
, B.PK AS 主键
, DECODE(T.NULLABLE, 'N', 'N') AS 可空
, T.DATA_DEFAULT AS 默认
, C.INDEXES AS 索引
, T.COLUMN_ID AS 列顺序
FROM USER_TAB_COLUMNS T -- 表列
LEFT JOIN USER_TAB_COMMENTS H -- 表注释
ON H.TABLE_NAME = T.TABLE_NAME
LEFT JOIN USER_COL_COMMENTS A -- 列注释
ON A.TABLE_NAME = T.TABLE_NAME
AND A.COLUMN_NAME = T.COLUMN_NAME
LEFT JOIN (
SELECT M.TABLE_NAME,M.COLUMN_NAME, 'Y' AS PK
FROM USER_CONS_COLUMNS M -- 约束对应列
INNER JOIN USER_CONSTRAINTS N -- 约束
ON M.CONSTRAINT_NAME = N.CONSTRAINT_NAME
WHERE N.CONSTRAINT_TYPE = 'P'
) B
ON B.TABLE_NAME = T.TABLE_NAME
AND B.COLUMN_NAME = T.COLUMN_NAME
LEFT JOIN (
SELECT TABLE_NAME, COLUMN_NAME, LISTAGG(INDEX_NAME, ',') WITHIN GROUP(ORDER BY INDEX_NAME) AS INDEXES
FROM USER_IND_COLUMNS
GROUP BY TABLE_NAME, COLUMN_NAME
) C
ON C.TABLE_NAME = T.TABLE_NAME
AND C.COLUMN_NAME = T.COLUMN_NAME
WHERE T.TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TABLES)
ORDER BY T.TABLE_NAME, T.COLUMN_ID
- SQLServer
/* 完成 */
SELECT Upper(C.TABLE_SCHEMA) AS 模式
, Upper(C.TABLE_NAME) AS 表名称
, M.TABCOMMENT AS 表注释
, Upper(C.COLUMN_NAME) AS 列名称
, M.COLCOMMENT AS 列注释
, Upper(C.DATA_TYPE) AS 数据类型
, ISNULL(C.CHARACTER_MAXIMUM_LENGTH,C.NUMERIC_PRECISION) AS 列长度
, C.NUMERIC_PRECISION AS 小数位数
, CASE WHEN P.COLUMN_NAME IS NULL THEN NULL ELSE 'Y' END AS 是否主键
, CASE WHEN SUBSTRING(C.IS_NULLABLE,1,1) = 'N' THEN 'N' ELSE NULL END AS 是否可为空
, C.COLUMN_DEFAULT AS 列默认值
, N.INDEXES AS 索引名称
, C.ORDINAL_POSITION AS 列顺序
FROM INFORMATION_SCHEMA.COLUMNS C
LEFT JOIN ( -- 表和列的注释
SELECT T.NAME AS TABNAME
, CONVERT(VARCHAR(1024),E1.VALUE) AS TABCOMMENT
, C.NAME AS COLNAME
, CONVERT(VARCHAR(1024),E2.VALUE) AS COLCOMMENT
FROM SYS.TABLES T -- 表
INNER JOIN SYS.COLUMNS C -- 列
ON C.OBJECT_ID = T.OBJECT_ID
LEFT JOIN SYS.EXTENDED_PROPERTIES E1 -- 系统属性表
ON E1.MAJOR_ID = T.OBJECT_ID
AND E1.CLASS = 1
AND E1.MINOR_ID = 0 -- 限制是表的注释
LEFT JOIN SYS.EXTENDED_PROPERTIES E2 -- 系统属性表
ON E2.MAJOR_ID = C.OBJECT_ID
AND E2.CLASS = 1
AND E2.MINOR_ID = C.COLUMN_ID -- 限制是列的注释
WHERE ObjectProperty(T.OBJECT_ID, 'IsUserTable') = 1 -- 限制为用户建立的表
) M
ON M.TABNAME = C.TABLE_NAME
AND M.COLNAME = C.COLUMN_NAME
LEFT JOIN ( -- 主键约束
SELECT TC.TABLE_SCHEMA, TC.TABLE_NAME, CC.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC -- 表约束(表名,约束名,约束类型)
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CC -- 约束使用的列(根据约束名关联)
ON TC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
) P
ON C.TABLE_SCHEMA = P.TABLE_SCHEMA
AND C.TABLE_NAME = P.TABLE_NAME
AND C.COLUMN_NAME = P.COLUMN_NAME
LEFT JOIN ( -- 索引
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, LEFT(INDEXES, LEN(INDEXES) - 1) AS INDEXES
FROM (
SELECT K.TABLE_SCHEMA
, K.TABLE_NAME
, K.COLUMN_NAME
, (SELECT N.CONSTRAINT_NAME + ','
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE N
WHERE N.TABLE_SCHEMA = K.TABLE_SCHEMA
AND N.TABLE_NAME = K.TABLE_NAME
AND N.COLUMN_NAME = K.COLUMN_NAME
FOR XML PATH('')) AS INDEXES
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
GROUP BY K.TABLE_SCHEMA, K.TABLE_NAME, K.COLUMN_NAME
) K
) N
ON C.TABLE_SCHEMA = N.TABLE_SCHEMA
AND C.TABLE_NAME = N.TABLE_NAME
AND C.COLUMN_NAME = N.COLUMN_NAME
ORDER BY C.TABLE_SCHEMA, C.TABLE_NAME, C.ORDINAL_POSITION
- PostgreSQL
/* 除了索引, 其他完成 */
SELECT
UPPER(C.TABLE_SCHEMA) AS 模式
, UPPER(C.TABLE_NAME) AS 表名称
, C.TAB_COMMENT AS 表注释
, UPPER(C.COLUMN_NAME) AS 列名称
, C.COL_COMMENT AS 列注释
, UPPER(C.DATA_TYPE) AS 列类型
, COALESCE(C.CHARACTER_MAXIMUM_LENGTH,C.NUMERIC_PRECISION) AS 列长度
, C.NUMERIC_SCALE AS 小数位数
, C.COL_PRIMARY AS 是否主键
, CASE WHEN SUBSTR(C.IS_NULLABLE,1,1) = 'N' THEN 'N'ELSE NULL END AS 是否可空
, CASE -- 字符串的默认值存储的是: 'N'::CHARACTER VARYING, 因此特殊处理
WHEN SUBSTR(C.COLUMN_DEFAULT,1,1) = ''''
THEN SUBSTR(C.COLUMN_DEFAULT,2, POSITION('''' IN SUBSTR(C.COLUMN_DEFAULT,2)) - 1)
ELSE C.COLUMN_DEFAULT
END AS 默认
, NULL AS 索引 -- PostgreSQL中的索引好麻烦,没找到合适的方法取
, C.ORDINAL_POSITION AS 字段顺序
FROM (
-- information_schema.columns 的定义: 追加表注释,列注释及索引信息
SELECT current_database()::information_schema.sql_identifier AS table_catalog, nc.nspname::information_schema.sql_identifier AS table_schema, c.relname::information_schema.sql_identifier AS table_name, a.attname::information_schema.sql_identifier AS column_name, a.attnum::information_schema.cardinal_number AS ordinal_position, pg_get_expr(ad.adbin, ad.adrelid)::information_schema.character_data AS column_default,
CASE
WHEN a.attnotnull OR (t.typtype = 'd'::"char" AND t.typnotnull) THEN 'NO'::text
ELSE 'YES'::text
END::information_schema.character_data AS is_nullable,
CASE
WHEN t.typtype = 'd'::"char" THEN
CASE
WHEN bt.typelem <> 0::oid AND bt.typlen = (-1) THEN 'ARRAY'::text
WHEN nbt.nspname = 'pg_catalog'::name THEN format_type(t.typbasetype, NULL::integer)
ELSE 'USER-DEFINED'::text
END
ELSE
CASE
WHEN t.typelem <> 0::oid AND t.typlen = (-1) THEN 'ARRAY'::text
WHEN nt.nspname = 'pg_catalog'::name THEN format_type(a.atttypid, NULL::integer)
ELSE 'USER-DEFINED'::text
END
END::information_schema.character_data AS data_type, information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS character_maximum_length, information_schema._pg_char_octet_length(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS character_octet_length, information_schema._pg_numeric_precision(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS numeric_precision, information_schema._pg_numeric_precision_radix(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS numeric_precision_radix, information_schema._pg_numeric_scale(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS numeric_scale, information_schema._pg_datetime_precision(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS datetime_precision, NULL::character varying::information_schema.character_data AS interval_type, NULL::character varying::information_schema.character_data AS interval_precision, NULL::character varying::information_schema.sql_identifier AS character_set_catalog, NULL::character varying::information_schema.sql_identifier AS character_set_schema, NULL::character varying::information_schema.sql_identifier AS character_set_name, NULL::character varying::information_schema.sql_identifier AS collation_catalog, NULL::character varying::information_schema.sql_identifier AS collation_schema, NULL::character varying::information_schema.sql_identifier AS collation_name,
CASE
WHEN t.typtype = 'd'::"char" THEN current_database()
ELSE NULL::name
END::information_schema.sql_identifier AS domain_catalog,
CASE
WHEN t.typtype = 'd'::"char" THEN nt.nspname
ELSE NULL::name
END::information_schema.sql_identifier AS domain_schema,
CASE
WHEN t.typtype = 'd'::"char" THEN t.typname
ELSE NULL::name
END::information_schema.sql_identifier AS domain_name, current_database()::information_schema.sql_identifier AS udt_catalog, COALESCE(nbt.nspname, nt.nspname)::information_schema.sql_identifier AS udt_schema, COALESCE(bt.typname, t.typname)::information_schema.sql_identifier AS udt_name, NULL::character varying::information_schema.sql_identifier AS scope_catalog, NULL::character varying::information_schema.sql_identifier AS scope_schema, NULL::character varying::information_schema.sql_identifier AS scope_name, NULL::integer::information_schema.cardinal_number AS maximum_cardinality, a.attnum::information_schema.sql_identifier AS dtd_identifier, 'NO'::character varying::information_schema.character_data AS is_self_referencing, 'NO'::character varying::information_schema.character_data AS is_identity, NULL::character varying::information_schema.character_data AS identity_generation, NULL::character varying::information_schema.character_data AS identity_start, NULL::character varying::information_schema.character_data AS identity_increment, NULL::character varying::information_schema.character_data AS identity_maximum, NULL::character varying::information_schema.character_data AS identity_minimum, NULL::character varying::information_schema.character_data AS identity_cycle, 'NEVER'::character varying::information_schema.character_data AS is_generated, NULL::character varying::information_schema.character_data AS generation_expression,
CASE
WHEN c.relkind = 'r'::"char" THEN 'YES'::text
ELSE 'NO'::text
END::information_schema.character_data AS is_updatable
-- 追加两个注释和是否为主键
, cast(obj_description(c.relfilenode,'pg_class') as varchar) AS tab_comment
, col_description(a.attrelid,a.attnum) AS col_comment
, case when con.conname is not null then 'Y' else null end as col_primary
FROM pg_attribute a
-- 追加是不是主键
LEFT JOIN pg_constraint con ON con.conrelid = a.attrelid AND con.conkey[] = a.attnum
LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum, pg_class c, pg_namespace nc, pg_type t
JOIN pg_namespace nt ON t.typnamespace = nt.oid
LEFT JOIN (pg_type bt
JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid) ON t.typtype = 'd'::"char" AND t.typbasetype = bt.oid WHERE a.attrelid = c.oid AND a.atttypid = t.oid AND nc.oid = c.relnamespace AND NOT pg_is_other_temp_schema(nc.oid) AND a.attnum > 0 AND NOT a.attisdropped AND (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char"])) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_table_privilege(c.oid, 'SELECT'::text) OR has_table_privilege(c.oid, 'INSERT'::text) OR has_table_privilege(c.oid, 'UPDATE'::text) OR has_table_privilege(c.oid, 'REFERENCES'::text))
) c
WHERE C.TABLE_NAME IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE')
AND C.TABLE_SCHEMA NOT IN ('information_schema','pg_catalog') -- 排除信息模式和系统本身元数据模式
AND C.TABLE_SCHEMA NOT LIKE 'pg|_%' escape '|' -- 排除pg系统表
AND C.TABLE_SCHEMA NOT LIKE 'gp|_%' escape '|' -- 排除gp系统表
ORDER BY C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_NAME, C.ORDINAL_POSITION
- Mariadb(参考MySQL)
- GreenPlum
/* 除了索引和分布键, 其他完成 */
SELECT
UPPER(C.TABLE_SCHEMA) AS 模式
, UPPER(C.TABLE_NAME) AS 表名称
, C.TAB_COMMENT AS 表注释
, UPPER(C.COLUMN_NAME) AS 列名称
, C.COL_COMMENT AS 列注释
, UPPER(C.DATA_TYPE) AS 列类型
, COALESCE(C.CHARACTER_MAXIMUM_LENGTH,C.NUMERIC_PRECISION) AS 列长度
, C.NUMERIC_SCALE AS 小数位数
, C.COL_PRIMARY AS 是否主键
, CASE WHEN SUBSTR(C.IS_NULLABLE,1,1) = 'N' THEN 'N'ELSE NULL END AS 是否可空
, CASE -- 字符串的默认值存储的是: 'N'::CHARACTER VARYING, 因此特殊处理
WHEN SUBSTR(C.COLUMN_DEFAULT,1,1) = ''''
THEN SUBSTR(C.COLUMN_DEFAULT,2, POSITION('''' IN SUBSTR(C.COLUMN_DEFAULT,2)) - 1)
ELSE C.COLUMN_DEFAULT
END AS 默认
, NULL AS 索引 -- PostgreSQL中的索引好麻烦,没找到合适的方法取
, C.ORDINAL_POSITION AS 字段顺序
FROM (
-- information_schema.columns 的定义: 追加表注释,列注释及索引信息
SELECT current_database()::information_schema.sql_identifier AS table_catalog, nc.nspname::information_schema.sql_identifier AS table_schema, c.relname::information_schema.sql_identifier AS table_name, a.attname::information_schema.sql_identifier AS column_name, a.attnum::information_schema.cardinal_number AS ordinal_position, pg_get_expr(ad.adbin, ad.adrelid)::information_schema.character_data AS column_default,
CASE
WHEN a.attnotnull OR (t.typtype = 'd'::"char" AND t.typnotnull) THEN 'NO'::text
ELSE 'YES'::text
END::information_schema.character_data AS is_nullable,
CASE
WHEN t.typtype = 'd'::"char" THEN
CASE
WHEN bt.typelem <> 0::oid AND bt.typlen = (-1) THEN 'ARRAY'::text
WHEN nbt.nspname = 'pg_catalog'::name THEN format_type(t.typbasetype, NULL::integer)
ELSE 'USER-DEFINED'::text
END
ELSE
CASE
WHEN t.typelem <> 0::oid AND t.typlen = (-1) THEN 'ARRAY'::text
WHEN nt.nspname = 'pg_catalog'::name THEN format_type(a.atttypid, NULL::integer)
ELSE 'USER-DEFINED'::text
END
END::information_schema.character_data AS data_type, information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS character_maximum_length, information_schema._pg_char_octet_length(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS character_octet_length, information_schema._pg_numeric_precision(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS numeric_precision, information_schema._pg_numeric_precision_radix(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS numeric_precision_radix, information_schema._pg_numeric_scale(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS numeric_scale, information_schema._pg_datetime_precision(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::information_schema.cardinal_number AS datetime_precision, NULL::character varying::information_schema.character_data AS interval_type, NULL::character varying::information_schema.character_data AS interval_precision, NULL::character varying::information_schema.sql_identifier AS character_set_catalog, NULL::character varying::information_schema.sql_identifier AS character_set_schema, NULL::character varying::information_schema.sql_identifier AS character_set_name, NULL::character varying::information_schema.sql_identifier AS collation_catalog, NULL::character varying::information_schema.sql_identifier AS collation_schema, NULL::character varying::information_schema.sql_identifier AS collation_name,
CASE
WHEN t.typtype = 'd'::"char" THEN current_database()
ELSE NULL::name
END::information_schema.sql_identifier AS domain_catalog,
CASE
WHEN t.typtype = 'd'::"char" THEN nt.nspname
ELSE NULL::name
END::information_schema.sql_identifier AS domain_schema,
CASE
WHEN t.typtype = 'd'::"char" THEN t.typname
ELSE NULL::name
END::information_schema.sql_identifier AS domain_name, current_database()::information_schema.sql_identifier AS udt_catalog, COALESCE(nbt.nspname, nt.nspname)::information_schema.sql_identifier AS udt_schema, COALESCE(bt.typname, t.typname)::information_schema.sql_identifier AS udt_name, NULL::character varying::information_schema.sql_identifier AS scope_catalog, NULL::character varying::information_schema.sql_identifier AS scope_schema, NULL::character varying::information_schema.sql_identifier AS scope_name, NULL::integer::information_schema.cardinal_number AS maximum_cardinality, a.attnum::information_schema.sql_identifier AS dtd_identifier, 'NO'::character varying::information_schema.character_data AS is_self_referencing, 'NO'::character varying::information_schema.character_data AS is_identity, NULL::character varying::information_schema.character_data AS identity_generation, NULL::character varying::information_schema.character_data AS identity_start, NULL::character varying::information_schema.character_data AS identity_increment, NULL::character varying::information_schema.character_data AS identity_maximum, NULL::character varying::information_schema.character_data AS identity_minimum, NULL::character varying::information_schema.character_data AS identity_cycle, 'NEVER'::character varying::information_schema.character_data AS is_generated, NULL::character varying::information_schema.character_data AS generation_expression,
CASE
WHEN c.relkind = 'r'::"char" THEN 'YES'::text
ELSE 'NO'::text
END::information_schema.character_data AS is_updatable
-- 追加两个注释和是否为主键
, cast(obj_description(c.relfilenode,'pg_class') as varchar) AS tab_comment
, col_description(a.attrelid,a.attnum) AS col_comment
, case when con.conname is not null then 'Y' else null end as col_primary
FROM pg_attribute a
-- 追加是不是主键
LEFT JOIN pg_constraint con ON con.conrelid = a.attrelid AND con.conkey[] = a.attnum
LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum, pg_class c, pg_namespace nc, pg_type t
JOIN pg_namespace nt ON t.typnamespace = nt.oid
LEFT JOIN (pg_type bt
JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid) ON t.typtype = 'd'::"char" AND t.typbasetype = bt.oid WHERE a.attrelid = c.oid AND a.atttypid = t.oid AND nc.oid = c.relnamespace AND NOT pg_is_other_temp_schema(nc.oid) AND a.attnum > 0 AND NOT a.attisdropped AND (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char"])) AND (pg_has_role(c.relowner, 'USAGE'::text) OR has_table_privilege(c.oid, 'SELECT'::text) OR has_table_privilege(c.oid, 'INSERT'::text) OR has_table_privilege(c.oid, 'UPDATE'::text) OR has_table_privilege(c.oid, 'REFERENCES'::text))
) c
WHERE C.TABLE_NAME IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE')
AND C.TABLE_SCHEMA NOT IN ('information_schema','pg_catalog') -- 排除信息模式和系统本身元数据模式
AND C.TABLE_SCHEMA NOT LIKE 'pg|_%' escape '|' -- 排除pg系统表
AND C.TABLE_SCHEMA NOT LIKE 'gp|_%' escape '|' -- 排除gp系统表
ORDER BY C.TABLE_CATALOG, C.TABLE_SCHEMA, C.TABLE_NAME, C.ORDINAL_POSITION
- Teradata
/* 完成 */
SELECT
TRIM(C.DatabaseName) AS 数据库
, TRIM(C.TableName) AS 表名称
, TRIM(T.CommentString) AS 表注释
, TRIM(C.ColumnName) AS 列名称
, TRIM(NVL(C.ColumnTitle,C.CommentString)) AS 列注释
/* Teradata® RDBMS Data Dictionary的P76页System View Columns Reference的ColumnType枚举 (B035-1092-061A)*/
/* 版本较旧,枚举值可能不全, 最新版手册没有找到这个枚举值 */
, CASE TRIM(C.ColumnType)
WHEN 'AT' THEN 'TIME'
WHEN 'BF' THEN 'BYTE'
WHEN 'BV' THEN 'VARBYTE'
WHEN 'CF' THEN 'CHAR'
WHEN 'CV' THEN 'VARCHAR'
WHEN 'D' THEN 'DECIMAL'
WHEN 'DA' THEN 'DATE'
WHEN 'DH' THEN 'INTERVAL DAY TO HOUR'
WHEN 'DM' THEN 'INTERVAL DAY TO MINUTE'
WHEN 'DS' THEN 'INTERVAL DAY TO SECOND'
WHEN 'DY' THEN 'INTERVAL DAY'
WHEN 'F' THEN 'FLOAT'
WHEN 'GF' THEN 'GRAPHIC'
WHEN 'GV' THEN 'VARGRAPHIC'
WHEN 'HM' THEN 'INTERVAL HOUR TO MINUTE'
WHEN 'HR' THEN 'INTERVAL HOUR'
WHEN 'HS' THEN 'INTERVAL HOUR TO SECOND'
WHEN 'I1' THEN 'BYTEINT'
WHEN 'I2' THEN 'SMALLINT'
WHEN 'I8' THEN 'BYTEINTEGER'
WHEN 'I' THEN 'INTEGER'
WHEN 'MI' THEN 'INTERVAL MINUTE'
WHEN 'MO' THEN 'INTERVAL MONTH'
WHEN 'MS' THEN 'INTERVAL MINUTE TO SECOND'
WHEN 'SC' THEN 'INTERVAL SECOND'
WHEN 'SZ' THEN 'TIMESTAMP WITH TIME ZONE'
WHEN 'TS' THEN 'TIMESTAMP'
WHEN 'TZ' THEN 'TIME WITH TIME ZONE'
WHEN 'YM' THEN 'INTERVAL YEAR TO MONTH'
WHEN 'YR' THEN 'INTERVAL YEAR'
ELSE C.ColumnType
END
, NVL(C.ColumnLength,C.DecimalTotalDigits) AS 数据长度
, C.DecimalFractionalDigits AS 小数位数
, NVL2(I.ColumnName,'Y',NULL) AS 是否主键
, CASE WHEN C.Nullable = 'N' THEN 'N' ELSE NULL END AS 是否可空
, C.DefaultValue AS 默认值
, H.Indexes AS 索引名称
, C.ColumnId AS 列顺序
FROM DBC.COLUMNS C -- 列元数据
INNER JOIN DBC.TABLES T -- 表元数据
ON T.DatabaseName = C.DatabaseName
AND T.TableName = C.TableName
AND T.TableKind = 'T'
LEFT JOIN DBC.INDICES I -- 索引
ON I.DatabaseName = C.DatabaseName
AND I.TableName = C.TableName
AND I.ColumnName = C.ColumnName
AND I.INDEXTYPE = 'K' -- 限制为主键
LEFT JOIN (
/* If Teradata's XML-services are installed (default since 14.10?) there's a function named XMLAGG */
SELECT DatabaseName, TableName, ColumnName
, TRIM(TRAILING ',' FROM (XMLAGG(TRIM(ColumnName)|| ',' ORDER BY ColumnPosition) (VARCHAR(10000)))) AS Indexes
FROM DBC.INDICES
WHERE IndexName IS NOT NULL
GROUP BY DatabaseName, TableName, ColumnName
) H
ON I.DatabaseName = H.DatabaseName
AND I.TableName = H.TableName
AND I.ColumnName = H.ColumnName
ORDER BY C.DatabaseName, C.TableName, C.ColumnId