请问这个查询语句怎么写？

Question

我用下面的查询语句可以得到表ANIMAL的字段、类型、长度等信息：
SELECT NVL(DATA_TYPE_MOD,' ') DATA_TYPE_MOD,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,NULLABLE,DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME='ANIMAL';
用下面的查询语句得到主键字段名：
SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME='ANIMAL' AND COLUMN_NAME IN (SELECT COLUMN_NAME FROM DBA_CONS_COLUMNS  WHERE CONSTRAINT_NAME IN (select CONSTRAINT_NAME from user_constraints where table_name='ANIMAL' AND CONSTRAINT_TYPE='P'));//得到外键字段名及类型长度等
SELECT NVL(DATA_TYPE_MOD,' ') DATA_TYPE_MOD,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,NULLABLE,DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME='ANIMAL' AND COLUMN_NAME IN (SELECT COLUMN_NAME FROM DBA_CONS_COLUMNS  WHERE CONSTRAINT_NAME IN (select CONSTRAINT_NAME from user_constraints where table_name='ANIMAL' AND CONSTRAINT_TYPE='R'));请问怎样把这三个查询合在一起，在返回的集合增加两个字段（PK_COL和FK_COL）,如果是主键在PK_COL列该行值为Y，如果是外键FK_COL列该行值为Y。
请问能做到吗？

Answer 1

SELECT NVL(a.DATA_TYPE_MOD,' ') DATA_TYPE_MOD,a.COLUMN_NAME,a.DATA_TYPE,a.DATA_LENGTH,a.NULLABLE,a.DATA_DEFAULT,
decode(c.CONSTRAINT_TYPE,'P','Y',null)
PK_COL,
decode(c.CONSTRAINT_TYPE,'R','Y',null)
FK_COL 
FROM USER_TAB_COLUMNS a,DBA_CONS_COLUMNS b,user_constraints c WHERE a.COLUMN_NAME=b.COLUMN_NAME(+) and b.CONSTRAINT_NAME=c.CONSTRAINT_NAME(+) and  a.TABLE_NAME='ANIMAL' and c.TABLE_NAME='ANIMAL'

Answer 2

SELECT NVL (a.data_type_mod, ' ') data_type_mod, a.column_name, a.data_type,
       a.data_length, a.nullable, a.data_default,
       DECODE (b.constraint_type, 'P', 'Y', NULL) pk_col,
       DECODE (b.constraint_type, 'R', 'Y', NULL) fk_col
  FROM user_tab_columns a,
       (SELECT d.table_name, d.constraint_name, d.constraint_type,
               c.column_name
          FROM dba_cons_columns c, user_constraints d
         WHERE d.constraint_name = c.constraint_name(+)) b
 WHERE a.column_name = b.column_name(+) AND a.table_name = 'ANIMAL'

Answer 3

SELECT NVL(a.DATA_TYPE_MOD,' ') DATA_TYPE_MOD,a.COLUMN_NAME,a.DATA_TYPE,a.DATA_LENGTH,a.NULLABLE,a.DATA_DEFAULT,
decode(c.CONSTRAINT_TYPE,'P','Y',null)
PK_COL,
decode(c.CONSTRAINT_TYPE,'R','Y',null)
FK_COL 
FROM USER_TAB_COLUMNS a,DBA_CONS_COLUMNS b,user_constraints c WHERE a.COLUMN_NAME=c.COLUMN_NAME(+) and c.CONSTRAINT_NAME=b.CONSTRAINT_NAME(+) and  a.TABLE_NAME='ANIMAL'

Answer 4

waterfirer(水清)，你好！
用你的方法出来的一方面有重复的，有时候还不对，比如没有主键只有2个外键，结果多出来那两个字段又成了主键

Answer 5

写错了，不要意思，这个试试吧
SELECT NVL (a.data_type_mod, ' ') data_type_mod, a.column_name, a.data_type,
       a.data_length, a.nullable, a.data_default,
       DECODE (b.constraint_type, 'P', 'Y', NULL) pk_col,
       DECODE (b.constraint_type, 'R', 'Y', NULL) fk_col
  FROM user_tab_columns a,
       (SELECT d.constraint_name, d.constraint_type, c.column_name
          FROM dba_cons_columns c, user_constraints d
         WHERE d.constraint_name = c.constraint_name(+)
               AND d.table_name = 'ANIMAL') b
 WHERE a.column_name = b.column_name(+) AND a.table_name = 'ANIMAL'

Answer 6

SELECT NVL(DATA_TYPE_MOD,' ') DATA_TYPE_MOD,
       COLUMN_NAME,
       DATA_TYPE,
       DATA_LENGTH,
       NULLABLE,
       DATA_DEFAULT,
       decode((select count(*) from USER_CONS_COLUMNS cons_col, USER_CONSTRAINTS cons
                   where cons.table_name='ANIMAL' AND cons.CONSTRAINT_TYPE='P'
                     and cons_col.constraint_name=cons.constraint_name
                     and cons_col.column_name=col.column_name), 0, ' ', 'Y') PK_COL,
       decode((select count(*) from USER_CONS_COLUMNS cons_col, USER_CONSTRAINTS cons
                   where cons.table_name='ANIMAL' AND cons.CONSTRAINT_TYPE='R'
                     and cons_col.constraint_name=cons.constraint_name
                     and cons_col.column_name=col.column_name), 0, ' ', 'Y') PK_COL
    FROM USER_TAB_COLUMNS col
    WHERE TABLE_NAME='ANIMAL';

Answer 7

SELECT NVL(DATA_TYPE_MOD,' ') DATA_TYPE_MOD,
       COLUMN_NAME,
       DATA_TYPE,
       DATA_LENGTH,
       NULLABLE,
       DATA_DEFAULT,
       decode((select count(*) from USER_CONS_COLUMNS cons_col, USER_CONSTRAINTS cons
                   where cons.table_name='ANIMAL' AND cons.CONSTRAINT_TYPE='P'
                     and cons_col.constraint_name=cons.constraint_name
                     and cons_col.column_name=col.column_name), 0, ' ', 'Y') PK_COL,
       decode((select count(*) from USER_CONS_COLUMNS cons_col, USER_CONSTRAINTS cons
                   where cons.table_name='ANIMAL' AND cons.CONSTRAINT_TYPE='R'
                     and cons_col.constraint_name=cons.constraint_name
                     and cons_col.column_name=col.column_name), 0, ' ', 'Y') FK_COL
    FROM USER_TAB_COLUMNS col
    WHERE TABLE_NAME='ANIMAL';

Answer 8

谢谢bobfang(匆匆过客) ,现在可以了。我想再问一个问题，接着上面的，用下面的
select b.table_name,
   b.column_name
   from user_constraints a,user_cons_columns b
    where a.table_name='ANIMAL'
    and a.r_constraint_name=b.constraint_name;
可以得到外键名称及外键对应的表名，我想和上面的查询合在一起，增加一个字段，在有外键的那行上则加外键对应的表名（对象名），也就是b.table_name，请问怎样能和上面的查询合在一起？谢谢！

Answer 9

SELECT NVL(DATA_TYPE_MOD,' ') DATA_TYPE_MOD,
       COLUMN_NAME,
       DATA_TYPE,
       DATA_LENGTH,
       NULLABLE,
       DATA_DEFAULT,
       decode((select count(*) from USER_CONS_COLUMNS cons_col, USER_CONSTRAINTS cons
                   where cons.table_name='ANIMAL' AND cons.CONSTRAINT_TYPE='P'
                     and cons_col.constraint_name=cons.constraint_name
                     and cons_col.column_name=col.column_name), 0, ' ', 'Y') PK_COL,
       decode((select count(*) from USER_CONS_COLUMNS cons_col, USER_CONSTRAINTS cons
                   where cons.table_name='ANIMAL' AND cons.CONSTRAINT_TYPE='R'
                     and cons_col.constraint_name=cons.constraint_name
                     and cons_col.column_name=col.column_name), 0, ' ', 'Y') FK_COL,
       (select min(refs.table_name) 
             from USER_CONS_COLUMNS cons_col, USER_CONSTRAINTS cons, USER_CONSTRAINTS refs
             where cons.table_name='ANIMAL' AND cons.CONSTRAINT_TYPE='R'
               and cons_col.constraint_name=cons.constraint_name
               and cons_col.column_name=col.column_name
               and refs.constraint_name=cons.r_constraint_name) ref_table_name
    FROM USER_TAB_COLUMNS col
    WHERE TABLE_NAME='ANIMAL';