我要通过系统表查询出整个系统表结构
得出如下字段 表名 表注释 字段名 字段类型 字段长度...是否主键 主键排列号 本人写出如下SQL SELECT A.TABLE_NAME,A.OWNER,
(case when a.column_id=1 then (select b.comments from ALL_tab_comments b where table_name=A.TABLE_NAME and b.OWNER='SA') else ' ' end) tab_comments,
A.COLUMN_ID,A.COLUMN_NAME,A.DATA_TYPE,
nvl((select c.comments from all_col_comments c where c.table_name =A.TABLE_NAME and c.column_name =A.COLUMN_NAME and c.OWNER='SA'),' ' ) col_comments,
A.DEFAULT_LENGTH,A.DATA_DEFAULT,....FROM ALL_TAB_COLUMNS A where A.OWNER='SA' 问题出在主键 和主键排列号上, 请教高手此处主键和主键排列号该如何写
(本人也写出了一段可执行无错误的SQL 但效率太慢 就是因为对ORALCE系统表不够熟悉,主键取的不好)
得出如下字段 表名 表注释 字段名 字段类型 字段长度...是否主键 主键排列号 本人写出如下SQL SELECT A.TABLE_NAME,A.OWNER,
(case when a.column_id=1 then (select b.comments from ALL_tab_comments b where table_name=A.TABLE_NAME and b.OWNER='SA') else ' ' end) tab_comments,
A.COLUMN_ID,A.COLUMN_NAME,A.DATA_TYPE,
nvl((select c.comments from all_col_comments c where c.table_name =A.TABLE_NAME and c.column_name =A.COLUMN_NAME and c.OWNER='SA'),' ' ) col_comments,
A.DEFAULT_LENGTH,A.DATA_DEFAULT,....FROM ALL_TAB_COLUMNS A where A.OWNER='SA' 问题出在主键 和主键排列号上, 请教高手此处主键和主键排列号该如何写
(本人也写出了一段可执行无错误的SQL 但效率太慢 就是因为对ORALCE系统表不够熟悉,主键取的不好)
SELECT S.TABLE_NAME,
TC.COMMENTS,
S.COLUMN_NAME,
S.DATA_TYPE,
S.DATA_LENGTH,
(DECODE(PP.COLUMN_NAME, NULL, 'not', 'yes')) IS_PK,
PP.POSITION
FROM ALL_TAB_COLS S,
ALL_TAB_COMMENTS TC,
(SELECT CC.COLUMN_NAME, CC.POSITION, C.OWNER, C.TABLE_NAME
FROM ALL_CONSTRAINTS C, ALL_CONS_COLUMNS CC
WHERE C.OWNER = CC.OWNER
AND C.CONSTRAINT_TYPE = 'P'
AND C.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
AND C.TABLE_NAME = CC.TABLE_NAME) PP
WHERE S.TABLE_NAME = TC.TABLE_NAME
AND S.TABLE_NAME = PP.TABLE_NAME(+)
AND S.COLUMN_NAME = PP.COLUMN_NAME(+)
AND S.OWNER = PP.OWNER(+)
AND S.OWNER = TC.OWNER
AND S.OWNER = USER;
TC.COMMENTS,
S.COLUMN_NAME,
S.DATA_TYPE,
S.DATA_LENGTH,
(DECODE(PP.COLUMN_NAME, NULL, 'not', 'yes')) IS_PK,
PP.POSITION
FROM ALL_TAB_COLS S,
ALL_TAB_COMMENTS TC,
(SELECT CC.COLUMN_NAME, CC.POSITION, C.OWNER, C.TABLE_NAME
FROM ALL_CONSTRAINTS C, ALL_CONS_COLUMNS CC
WHERE C.OWNER = CC.OWNER
AND C.CONSTRAINT_TYPE = 'P'
AND C.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
AND C.TABLE_NAME = CC.TABLE_NAME) PP
WHERE S.TABLE_NAME = TC.TABLE_NAME
AND S.TABLE_NAME = PP.TABLE_NAME(+)
AND S.COLUMN_NAME = PP.COLUMN_NAME(+)
AND S.OWNER = PP.OWNER(+)
AND S.OWNER = TC.OWNER
AND S.OWNER = USER;