主流数据库 表结构 批量查询

ORACLE

SELECT
'TRD_CAMS' AS OWNER,
RTRIM(A.TABLE_NAME) AS TABLE_NAME,
RTRIM(A.COLUMN_NAME) AS COLUMN_NAME,
RTRIM(A.DATA_TYPE) AS DATA_TYPE,
RTRIM(A.DATA_TYPE_MOD) AS DATA_TYPE_MOD,
RTRIM(A.DATA_TYPE_OWNER) AS DATA_TYPE_OWNER,
A.DATA_LENGTH,
A.DATA_PRECISION,
A.DATA_SCALE,
RTRIM(A.NULLABLE) AS NULLABLE,
A.COLUMN_ID,
A.DEFAULT_LENGTH,
A.NUM_DISTINCT,
A.DENSITY,
A.NUM_NULLS,
A.NUM_BUCKETS,
A.LAST_ANALYZED,
A.SAMPLE_SIZE,
RTRIM(A.CHARACTER_SET_NAME) AS CHARACTER_SET_NAME,
A.CHAR_COL_DECL_LENGTH,
RTRIM(A.GLOBAL_STATS) AS GLOBAL_STATS,
RTRIM(A.USER_STATS) AS USER_STATS,
A.AVG_COL_LEN,
A.CHAR_LENGTH,
RTRIM(A.CHAR_USED) AS CHAR_USED,
RTRIM(A.V80_FMT_IMAGE) AS V80_FMT_IMAGE,
RTRIM(A.DATA_UPGRADED) AS DATA_UPGRADED,
RTRIM(A.HISTOGRAM) AS HISTOGRAM,
CASE WHEN B.OWNER IS NOT NULL AND B.TABLE_NAME IS NOT NULL
          AND B.COLUMN_NAME IS NOT NULL 
	 THEN 'PRI' 
	 END AS COLUMN_KEY,
RTRIM(D.COMMENTS) AS COLUMN_COMMENT
FROM SYS.USER_TAB_COLUMNS A
LEFT JOIN(SELECT DISTINCT CU.* 
FROM SYS.USER_CONS_COLUMNS CU,SYS.USER_CONSTRAINTS AU 
WHERE CU.CONSTRAINT_NAME=AU.CONSTRAINT_NAME 
AND AU.CONSTRAINT_TYPE='P') B 
ON  A.TABLE_NAME=B.TABLE_NAME
AND A.COLUMN_NAME=B.COLUMN_NAME 
LEFT JOIN SYS.USER_COL_COMMENTS D
ON  A.TABLE_NAME = D.TABLE_NAME
AND A.COLUMN_NAME = D.COLUMN_NAME

SQLSERVER

SELECT
RTRIM(A.TABLE_CATALOG) AS TABLE_CATALOG,
RTRIM(A.TABLE_SCHEMA) AS TABLE_SCHEMA,
RTRIM(A.TABLE_NAME) AS TABLE_NAME,
RTRIM(A.COLUMN_NAME) AS COLUMN_NAME,
A.ORDINAL_POSITION,
RTRIM(A.COLUMN_DEFAULT) AS COLUMN_DEFAULT,
RTRIM(A.IS_NULLABLE) AS IS_NULLABLE,
RTRIM(A.DATA_TYPE) AS DATA_TYPE,
A.CHARACTER_MAXIMUM_LENGTH,
A.CHARACTER_OCTET_LENGTH,
A.NUMERIC_PRECISION,
A.NUMERIC_PRECISION_RADIX,
A.NUMERIC_SCALE,
A.DATETIME_PRECISION,
RTRIM(A.CHARACTER_SET_CATALOG) AS CHARACTER_SET_CATALOG,
RTRIM(A.CHARACTER_SET_SCHEMA) AS CHARACTER_SET_SCHEMA,
RTRIM(A.CHARACTER_SET_NAME) AS CHARACTER_SET_NAME,
RTRIM(A.COLLATION_CATALOG) AS COLLATION_CATALOG,
RTRIM(A.COLLATION_SCHEMA) AS COLLATION_SCHEMA,
RTRIM(A.COLLATION_NAME) AS COLLATION_NAME,
RTRIM(A.DOMAIN_CATALOG) AS DOMAIN_CATALOG,
RTRIM(A.DOMAIN_SCHEMA) AS DOMAIN_SCHEMA,
RTRIM(A.DOMAIN_NAME) AS DOMAIN_NAME,
CASE WHEN B.TABLE_SCHEMA IS NOT NULL AND B.TABLE_NAME IS NOT NULL AND B.COLUMN_NAME IS NOT NULL
     THEN 'PRI'
END AS COLUMN_KEY,
D.COLUMN_COMMENT AS COLUMN_COMMENT
FROM information_schema.COLUMNS A
LEFT JOIN (SELECT DISTINCT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE) B
ON A.TABLE_SCHEMA = B.TABLE_SCHEMA
AND A.TABLE_NAME = B.TABLE_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME
LEFT JOIN 
(SELECT DISTINCT 
       S.NAME AS SCHEMA_NAME
      ,A.NAME AS TABLE_NAME
      ,B.NAME AS COLUMN_NAME
      ,C.VALUE AS COLUMN_COMMENT 
FROM SYS.SCHEMAS S
INNER JOIN SYS.TABLES A
ON S.SCHEMA_ID = A.SCHEMA_ID
INNER JOIN SYS.COLUMNS B ON A.OBJECT_ID=B.OBJECT_ID
LEFT JOIN SYS.EXTENDED_PROPERTIES C ON B.OBJECT_ID=C.MAJOR_ID
AND B.COLUMN_ID = C.MINOR_ID) D
ON A.TABLE_SCHEMA = D.SCHEMA_NAME
AND A.TABLE_NAME = D.TABLE_NAME
AND A.COLUMN_NAME = D.COLUMN_NAME

DB2

SELECT
RTRIM(A.TABSCHEMA) AS TABSCHEMA,
RTRIM(A.TABNAME) AS TABNAME,
RTRIM(A.COLNAME) AS COLNAME,
A.COLNO,
RTRIM(A.TYPESCHEMA) AS TYPESCHEMA,
RTRIM(A.TYPENAME) AS TYPENAME,
A.LENGTH,
A.SCALE,
CAST(A.DEFAULT AS VARCHAR(4000)) AS DEFAULT1,
RTRIM(A.NULLS) AS NULLS,
A.CODEPAGE,
RTRIM(A.COLLATIONSCHEMA) AS COLLATIONSCHEMA,
RTRIM(A.COLLATIONNAME) AS COLLATIONNAME,
RTRIM(A.LOGGED) AS LOGGED,
RTRIM(A.COMPACT) AS COMPACT,
A.COLCARD,
RTRIM(A.HIGH2KEY) AS HIGH2KEY,
RTRIM(A.LOW2KEY) AS LOW2KEY,
A.AVGCOLLEN,
A.KEYSEQ,
A.PARTKEYSEQ,
A.NQUANTILES,
A.NMOSTFREQ,
A.NUMNULLS,
RTRIM(A.TARGET_TYPESCHEMA) AS TARGET_TYPESCHEMA,
RTRIM(A.TARGET_TYPENAME) AS TARGET_TYPENAME,
RTRIM(A.SCOPE_TABSCHEMA) AS SCOPE_TABSCHEMA,
RTRIM(A.SCOPE_TABNAME) AS SCOPE_TABNAME,
RTRIM(A.SOURCE_TABSCHEMA) AS SOURCE_TABSCHEMA,
RTRIM(A.SOURCE_TABNAME) AS SOURCE_TABNAME,
RTRIM(A.DL_FEATURES) AS DL_FEATURES,
RTRIM(A.SPECIAL_PROPS) AS SPECIAL_PROPS,
RTRIM(A.HIDDEN) AS HIDDEN,
A.INLINE_LENGTH,
A.PCTINLINED,
RTRIM(A.IDENTITY) AS IDENTITY,
RTRIM(A.ROWCHANGETIMESTAMP) AS ROWCHANGETIMESTAMP,
RTRIM(A.GENERATED) AS GENERATED,
RTRIM(A.COMPRESS) AS COMPRESS,
A.AVGDISTINCTPERPAGE,
A.PAGEVARIANCERATIO,
A.SUB_COUNT,
A.SUB_DELIM_LENGTH,
A.AVGCOLLENCHAR,
RTRIM(A.IMPLICITVALUE) AS IMPLICITVALUE,
RTRIM(A.SECLABELNAME) AS SECLABELNAME,
RTRIM(A.REMARKS) AS REMARKS,
CASE WHEN B.NAME IS NOT NULL THEN 'PRI' END  AS COLUMN_KEY
FROM SYSCAT.COLUMNS A
LEFT JOIN (SELECT * FROM SYSIBM.SYSCOLUMNS WHERE KEYSEQ IS NOT NULL) B
ON A.TABSCHEMA = B.TBCREATOR
AND A.TABNAME = B.TBNAME
AND A.COLNAME=B.NAME

DB2 AS400

SELECT
RTRIM(A.TABLE_CATALOG) AS TABLE_CATALOG,
RTRIM(A.TABLE_SCHEMA) AS TABLE_SCHEMA,
RTRIM(A.TABLE_NAME) AS TABLE_NAME,
RTRIM(A.COLUMN_NAME) AS COLUMN_NAME,
A.ORDINAL_POSITION,
RTRIM(A.IS_NULLABLE) AS IS_NULLABLE,
RTRIM(A.DATA_TYPE) AS DATA_TYPE,
A.CHARACTER_MAXIMUM_LENGTH,
A.CHARACTER_OCTET_LENGTH,
A.NUMERIC_PRECISION,
A.NUMERIC_PRECISION_RADIX,
A.NUMERIC_SCALE,
A.DATETIME_PRECISION,
RTRIM(A.INTERVAL_TYPE) AS INTERVAL_TYPE,
A.INTERVAL_PRECISION,
RTRIM(A.CHARACTER_SET_CATALOG) AS CHARACTER_SET_CATALOG,
RTRIM(A.CHARACTER_SET_SCHEMA) AS CHARACTER_SET_SCHEMA,
RTRIM(A.CHARACTER_SET_NAME) AS CHARACTER_SET_NAME,
RTRIM(A.COLLATION_CATALOG) AS COLLATION_CATALOG,
RTRIM(A.COLLATION_SCHEMA) AS COLLATION_SCHEMA,
RTRIM(A.COLLATION_NAME) AS COLLATION_NAME,
RTRIM(A.DOMAIN_CATALOG) AS DOMAIN_CATALOG,
RTRIM(A.DOMAIN_SCHEMA) AS DOMAIN_SCHEMA,
RTRIM(A.DOMAIN_NAME) AS DOMAIN_NAME,
RTRIM(A.UDT_CATALOG) AS UDT_CATALOG,
RTRIM(A.UDT_SCHEMA) AS UDT_SCHEMA,
RTRIM(A.UDT_NAME) AS UDT_NAME,
RTRIM(A.SCOPE_CATALOG) AS SCOPE_CATALOG,
RTRIM(A.SCOPE_SCHEMA) AS SCOPE_SCHEMA,
RTRIM(A.SCOPE_NAME) AS SCOPE_NAME,
A.MAXIMUM_CARDINALITY,
RTRIM(A.DTD_IDENTIFIER) AS DTD_IDENTIFIER,
RTRIM(A.IS_SELF_REFERENCING) AS IS_SELF_REFERENCING,
CASE WHEN B.IS_IDENTITY='YES' THEN 'PRI' END AS COLUMN_KEY
--B.LONG_COMMENT AS COLUMN_COMMENT
FROM SYSIBM.COLUMNS A
LEFT JOIN (SELECT DISTINCT
                 COLUMN_NAME
				,TABLE_NAME
				,TABLE_SCHEMA
				,IS_IDENTITY
				,CAST(LONG_COMMENT AS VARCHAR(4000)) AS LONG_COMMENT
				FROM INFORMATION_SCHEMA.SYSCOLUMNS) B
ON A.COLUMN_NAME=B.COLUMN_NAME
AND A.TABLE_NAME=B.TABLE_NAME
AND A.TABLE_SCHEMA=B.TABLE_SCHEMA

MYSQL

SELECT
RTRIM(TABLE_CATALOG) AS TABLE_CATALOG,
RTRIM(TABLE_SCHEMA) AS TABLE_SCHEMA,
RTRIM(TABLE_NAME) AS TABLE_NAME,
RTRIM(COLUMN_NAME) AS COLUMN_NAME,
ORDINAL_POSITION,
RTRIM(COLUMN_DEFAULT) AS COLUMN_DEFAULT,
RTRIM(IS_NULLABLE) AS IS_NULLABLE,
RTRIM(DATA_TYPE) AS DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
CHARACTER_OCTET_LENGTH,
NUMERIC_PRECISION,
NUMERIC_SCALE,
DATETIME_PRECISION,
RTRIM(CHARACTER_SET_NAME) AS CHARACTER_SET_NAME,
RTRIM(COLLATION_NAME) AS COLLATION_NAME,
RTRIM(COLUMN_TYPE) AS COLUMN_TYPE,
RTRIM(COLUMN_KEY) AS COLUMN_KEY,
RTRIM(EXTRA) AS EXTRA,
RTRIM(PRIVILEGES) AS PRIVILEGES,
RTRIM(COLUMN_COMMENT) AS COLUMN_COMMENT
FROM information_schema.COLUMNS;