首页 > 数据库技术 > 详细

Oracle 查询表结构

时间:2015-06-15 16:04:32      阅读:308      评论:0      收藏:0      [点我收藏+]
--查询字段名称,字段类型,字段注释
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 查询表结构

原文:http://www.cnblogs.com/sallet/p/4577289.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!