日期:2014-05-17  浏览次数:20944 次

★★★ 讨论Oracle数据字典查询SQL (有点小问题恳请指正,谢谢!)
SELECT  
A.COLUMN_ID   AS   COLUMNORDERID,
A.COLUMN_NAME   AS   COLUMNNAME,
C.CONSTRAINT_NAME,
D1.CONSTRAINT_TYPE,
D1.R_CONSTRAINT_NAME,
CASE   WHEN   EXISTS   (SELECT   *   FROM   USER_CONSTRAINTS   WHERE   CONSTRAINT_TYPE   =   'P '   AND   CONSTRAINT_NAME   IN   (SELECT   CONSTRAINT_NAME   FROM   USER_CONS_COLUMNS   WHERE   COLUMN_NAME   =   A.COLUMN_NAME))   THEN   '√ '   ELSE   ' '   END   AS   PK,
CASE   WHEN   (D2.TABLE_NAME|| '. '||D2.COLUMN_NAME)= '. 'THEN   ' '   ELSE   (D2.TABLE_NAME|| '. '||D2.COLUMN_NAME)   END   AS   FK,
A.DATA_TYPE   AS   DATATYPE,
CASE   WHEN   A.DATA_PRECISION   IS   NULL   THEN   TO_CHAR(A.DATA_LENGTH)   ELSE   TO_CHAR(A.DATA_PRECISION)|| ', '||TO_CHAR(A.DATA_SCALE)   END   AS   DATALENGTH,
CASE   WHEN   A.NULLABLE   =   'Y '   THEN   '√ '   ELSE   ' '   END   AS   NULLABLE,
A.DATA_DEFAULT   AS   DATADEFAULT,
B.COMMENTS   AS   COLUMNDESCRIPTION
FROM   USER_TAB_COLUMNS   A   ,USER_COL_COMMENTS   B,USER_CONS_COLUMNS   C,USER_CONSTRAINTS   D1,USER_CONS_COLUMNS   D2
WHERE  
A.TABLE_NAME=B.TABLE_NAME   AND  
A.COLUMN_NAME=B.COLUMN_NAME   AND  
A.COLUMN_NAME=C.COLUMN_NAME(+)   AND
C.CONSTRAINT_NAME   =   D1.CONSTRAINT_NAME(+)   AND
D1.R_CONSTRAINT_NAME   =   D2.CONSTRAINT_NAME(+)   AND
A.TABLE_NAME= '表名 '
ORDER   BY   A.COLUMN_ID

/*
      您用您的表名替换以上SQL中 "表名 ",即可查询出当前表的相关信息.
      序号/字段名/PK/FK/数据类型/长度/是否为空/默认值/字段描述

      但存在以下问题:
      因为要把所引用的外键显示在外键后,所以上面的左连接可能有点混乱.结果就会有偏差:如,一字段为主键,肯定不为空,那查询结果便会有两条.因为在表USER_CONS_COLUMNS中有两条相关列的记录,同样,如果有多条,那结果就更离谱了.  
      其他问题暂未发现.
      恳请多多指正!谢谢!
*/



------解决方案--------------------
SELECT
A.COLUMN_ID AS COLUMNORDERID,
A.COLUMN_NAME AS COLUMNNAME,
C.CONSTRAINT_NAME,
D1.CONSTRAINT_TYPE,
D1.R_CONSTRAINT_NAME,
CASE WHEN EXISTS (SELECT * FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'P ' AND CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM USER_CONS_COLUMNS WHERE COLUMN_NAME = A.COLUMN_NAME)) THEN '¡Ì ' ELSE ' ' END AS PK,
CASE WHEN (D2.TABLE_NAME|| '. '||D2.COLUMN_NAME)= '. 'THEN ' ' ELSE (D2.TABLE_NAME|| '. '||D2.COLUMN_NAME) END AS FK,
A.DATA_TYPE AS DATATYPE,
CASE WHEN A.DATA_PRECISION IS NULL THEN TO_CHAR(A.DATA_LENGTH) ELSE TO_CHAR(A.DATA_PRECISION)|| ', '||TO_CHAR(A.DATA_SCALE) END AS DATALENGTH,
CASE WHEN A.NULLABLE = 'Y ' THEN '¡Ì ' ELSE ' ' END AS NULLABLE,
A.DATA_DEFAULT AS DATADEFAULT,
B.COMMENTS AS COLUMNDESCRIPTION
FROM USER_TAB_COLUMNS A ,USER_COL_COMMENTS B,USER_CONS_COLUMNS C,USER_CONSTRAINTS D1,USER_CONS_COLUMNS D2
WHERE
A.TABLE_NAME=B.TABLE_NAME AND
A.COLUMN_NAME=B.COLUMN_NAME AND
A.COLUMN_NAME=C.COLUMN_NAME(+) AND a.table_name = c.table_name(+) and c.position(+)= '1 ' and
c.CONSTRAINT_NAME = D1.CONSTRAINT_NAME(+) AND c.table_name = d1.table_name(+) and
D1.R_CONSTRAINT_NAME = D2.CONSTRAIN