Oracle 查询表结构

--查询字段名称，字段类型，字段注释
SELECT DD.*, EE.CONSTRAINT_TYPE
  FROM (SELECT T.COLUMN_ID,
               T.COLUMN_NAME,
               (CASE
                 WHEN (T.DATA_TYPE = ‘VARCHAR2‘ OR T.DATA_TYPE = ‘RAW‘) THEN
                  T.DATA_TYPE || ‘(‘ || T.DATA_LENGTH || ‘)‘
                 WHEN (T.DATA_TYPE = ‘NUMBER‘ AND
                      T.DATA_PRECISION IS NOT NULL) THEN
                  T.DATA_TYPE || ‘(‘ || T.DATA_PRECISION || ‘,‘ ||
                  T.DATA_SCALE || ‘)‘
                 ELSE
                  T.DATA_TYPE
               END) AS DATA_TYPE,
               B.COMMENTS,
               T.NULLABLE
          FROM USER_TAB_COLUMNS T
         INNER JOIN ALL_COL_COMMENTS B
            ON B.COLUMN_NAME = T.COLUMN_NAME
           AND T.TABLE_NAME = B.TABLE_NAME
           AND T.TABLE_NAME = UPPER(‘T_article‘)) DD
  LEFT JOIN

 (SELECT WM_CONCAT(CC.CONSTRAINT_TYPE) AS CONSTRAINT_TYPE, CC.COLUMN_NAME
    FROM (SELECT A.COLUMN_NAME, A.OWNER, A.TABLE_NAME, B.CONSTRAINT_TYPE
            FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B
           WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME --AND b.CONSTRAINT_TYPE =‘P‘
             AND A.TABLE_NAME = UPPER(‘T_article‘)) CC
   GROUP BY CC.COLUMN_NAME) EE
    ON EE.COLUMN_NAME = DD.COLUMN_NAME
 ORDER BY DD.COLUMN_ID;

--查询表注释
SELECT T.TABLE_NAME, T.COMMENTS, T.TABLE_TYPE FROM USER_TAB_COMMENTS T;

--查询表字段约束
(
  SELECT WM_CONCAT(CC.CONSTRAINT_TYPE) AS CONSTRAINT_TYPE,
         CC.COLUMN_NAME,
         CC.OWNER,
         CC.TABLE_NAME
    FROM (SELECT A.COLUMN_NAME, A.OWNER, A.TABLE_NAME, B.CONSTRAINT_TYPE
            FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B
           WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME --AND b.CONSTRAINT_TYPE =‘P‘
             AND A.TABLE_NAME = ‘T_WEIXIN_REMIND_FUND_CHANGE‘) CC
   GROUP BY CC.COLUMN_NAME, CC.OWNER, CC.TABLE_NAME)

Oracle 查询表结构

标签：