一、数据库驱动类、端口、默认用户名密码
| 数据库 | 驱动 | 端口 | 用户名 | 密码 |
|---|---|---|---|---|
| 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连接字符串
| 数据库 | 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[1] = 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[1] = 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