代码为:SELECT NULL "COLUMN_ID",
NULL "COLUMN_NAME",
NULL "COLUMN_TYPE",
NULL "LENGTH",
NULL "DATA_DEFAULT",
NULL "NULL",
NULL "PK",
TABLE_NAME
FROM USER_TAB_COLUMNS
UNION SELECT COLUMN_ID,
UT.COLUMN_NAME,
DATA_TYPE,
CHAR_LENGTH,
long_to_varchar2(UT.TABLE_NAME,UT.COLUMN_NAME,'DATA_DEFAULT','USER_TAB_COLUMNS',USER)"DATA_DEFAULT",
NULLABLE,
DECODE(UT.COLUMN_NAME,
CS.COLUMN_NAME,'Y','N') "IS_PAR_KEY",
UT.TABLE_NAME
FROM USER_TAB_COLUMNS UT,
(
SELECT UC.TABLE_NAME,
UCS.COLUMN_NAME
FROM USER_CONSTRAINTS UC,
USER_CONS_COLUMNS UCS
WHERE UC.TABLE_NAME = UCS.TABLE_NAME
AND UC.CONSTRAINT_NAME = UCS.CONSTRAINT_NAME
AND UC.CONSTRAINT_TYPE = 'P'
)CS
WHERE UT.TABLE_NAME = CS.TABLE_NAME(+)
ORDER BY TABLE_NAME,1 ASC;
重复部分:
1 VC_CALLINGNUM VARCHAR2 21 N N TB_RT_ROUTEPOLICY
1 VC_CALLINGNUM VARCHAR2 21 N Y TB_RT_ROUTEPOLICY
2 VC_CUSTOMER VARCHAR2 21 N N TB_RT_ROUTEPOLICY
2 VC_CUSTOMER VARCHAR2 21 N Y TB_RT_ROUTEPOLICY
3 VC_PREFIXCODE VARCHAR2 21 N N TB_RT_ROUTEPOLICY
3 VC_PREFIXCODE VARCHAR2 21 N Y TB_RT_ROUTEPOLICY
4 INT_INTERVALID NUMBER 0 N N TB_RT_ROUTEPOLICY
4 INT_INTERVALID NUMBER 0 N Y TB_RT_ROUTEPOLICY
5 INT_ROUTEID NUMBER 0 N N TB_RT_ROUTEPOLICY
5 INT_ROUTEID NUMBER 0 N Y TB_RT_ROUTEPOLICY
6 INT_DAYOFWEEK NUMBER 0 N N TB_RT_ROUTEPOLICY
6 INT_DAYOFWEEK NUMBER 0 N Y TB_RT_ROUTEPOLICY
7 INT_TRAFFICASSIGNMODE NUMBER 0 N N TB_RT_ROUTEPOLICY重复的不是很多,我手动删除了,但是不知道为什么会重复。
NULL "COLUMN_NAME",
NULL "COLUMN_TYPE",
NULL "LENGTH",
NULL "DATA_DEFAULT",
NULL "NULL",
NULL "PK",
TABLE_NAME
FROM USER_TAB_COLUMNS
UNION SELECT COLUMN_ID,
UT.COLUMN_NAME,
DATA_TYPE,
CHAR_LENGTH,
long_to_varchar2(UT.TABLE_NAME,UT.COLUMN_NAME,'DATA_DEFAULT','USER_TAB_COLUMNS',USER)"DATA_DEFAULT",
NULLABLE,
DECODE(UT.COLUMN_NAME,
CS.COLUMN_NAME,'Y','N') "IS_PAR_KEY",
UT.TABLE_NAME
FROM USER_TAB_COLUMNS UT,
(
SELECT UC.TABLE_NAME,
UCS.COLUMN_NAME
FROM USER_CONSTRAINTS UC,
USER_CONS_COLUMNS UCS
WHERE UC.TABLE_NAME = UCS.TABLE_NAME
AND UC.CONSTRAINT_NAME = UCS.CONSTRAINT_NAME
AND UC.CONSTRAINT_TYPE = 'P'
)CS
WHERE UT.TABLE_NAME = CS.TABLE_NAME(+)
ORDER BY TABLE_NAME,1 ASC;
重复部分:
1 VC_CALLINGNUM VARCHAR2 21 N N TB_RT_ROUTEPOLICY
1 VC_CALLINGNUM VARCHAR2 21 N Y TB_RT_ROUTEPOLICY
2 VC_CUSTOMER VARCHAR2 21 N N TB_RT_ROUTEPOLICY
2 VC_CUSTOMER VARCHAR2 21 N Y TB_RT_ROUTEPOLICY
3 VC_PREFIXCODE VARCHAR2 21 N N TB_RT_ROUTEPOLICY
3 VC_PREFIXCODE VARCHAR2 21 N Y TB_RT_ROUTEPOLICY
4 INT_INTERVALID NUMBER 0 N N TB_RT_ROUTEPOLICY
4 INT_INTERVALID NUMBER 0 N Y TB_RT_ROUTEPOLICY
5 INT_ROUTEID NUMBER 0 N N TB_RT_ROUTEPOLICY
5 INT_ROUTEID NUMBER 0 N Y TB_RT_ROUTEPOLICY
6 INT_DAYOFWEEK NUMBER 0 N N TB_RT_ROUTEPOLICY
6 INT_DAYOFWEEK NUMBER 0 N Y TB_RT_ROUTEPOLICY
7 INT_TRAFFICASSIGNMODE NUMBER 0 N N TB_RT_ROUTEPOLICY重复的不是很多,我手动删除了,但是不知道为什么会重复。
嗯?什么意思?
要我执行一下你给的SQL语句?
难道是数据库的原因?
NULL "COLUMN_NAME",
NULL "COLUMN_TYPE",
NULL "LENGTH",
NULL "DATA_DEFAULT",
NULL "NULL",
NULL "PK",
TABLE_NAME
FROM USER_TAB_COLUMNS
UNION SELECT COLUMN_ID,
UT.COLUMN_NAME,
DATA_TYPE,
CHAR_LENGTH,
long_to_varchar2(UT.TABLE_NAME,UT.COLUMN_NAME,'DATA_DEFAULT','USER_TAB_COLUMNS',USER)"DATA_DEFAULT",
NULLABLE,
DECODE(UT.COLUMN_NAME,
CS.COLUMN_NAME,'Y','N') "IS_PAR_KEY",
UT.TABLE_NAME
FROM USER_TAB_COLUMNS UT,
(
SELECT UC.TABLE_NAME,
UCS.COLUMN_NAME
FROM USER_CONSTRAINTS UC,
USER_CONS_COLUMNS UCS
WHERE UC.TABLE_NAME = UCS.TABLE_NAME
AND UC.CONSTRAINT_NAME = UCS.CONSTRAINT_NAME
AND UC.CONSTRAINT_TYPE = 'P'
)CS
WHERE UT.TABLE_NAME = CS.TABLE_NAME(+)
ORDER BY TABLE_NAME,1 ASC;
相当于两次查询,
然后第一遍许多字段都人为置成null了,本身用union会滤去重复的,但你置成了null,就不是重复的了
"COLUMN_NAME",
"COLUMN_TYPE",
"LENGTH",
"DATA_DEFAULT",
"NULL",
"PK",
TABLE_NAME
FROM USER_TAB_COLUMNS
UNION SELECT COLUMN_ID,
UT.COLUMN_NAME,
DATA_TYPE,
CHAR_LENGTH,
long_to_varchar2(UT.TABLE_NAME,UT.COLUMN_NAME,'DATA_DEFAULT','USER_TAB_COLUMNS',USER)"DATA_DEFAULT",
NULLABLE,
DECODE(UT.COLUMN_NAME,
CS.COLUMN_NAME,'Y','N') "IS_PAR_KEY",
UT.TABLE_NAME
FROM USER_TAB_COLUMNS UT,
(
SELECT UC.TABLE_NAME,
UCS.COLUMN_NAME
FROM USER_CONSTRAINTS UC,
USER_CONS_COLUMNS UCS
WHERE UC.TABLE_NAME = UCS.TABLE_NAME
AND UC.CONSTRAINT_NAME = UCS.CONSTRAINT_NAME
AND UC.CONSTRAINT_TYPE = 'P'
)CS
WHERE UT.TABLE_NAME = CS.TABLE_NAME(+)
ORDER BY TABLE_NAME,1 ASC;
"COLUMN_NAME",
"COLUMN_TYPE",
"LENGTH",
"DATA_DEFAULT",
"NULL",
"PK",
TABLE_NAME
FROM USER_TAB_COLUMNS
UNION SELECT distinct COLUMN_ID,
UT.COLUMN_NAME,
DATA_TYPE,
CHAR_LENGTH,
long_to_varchar2(UT.TABLE_NAME,UT.COLUMN_NAME,'DATA_DEFAULT','USER_TAB_COLUMNS',USER)"DATA_DEFAULT",
NULLABLE,
DECODE(UT.COLUMN_NAME,
CS.COLUMN_NAME,'Y','N') "IS_PAR_KEY",
UT.TABLE_NAME
FROM USER_TAB_COLUMNS UT,
(
SELECT UC.TABLE_NAME,
UCS.COLUMN_NAME
FROM USER_CONSTRAINTS UC,
USER_CONS_COLUMNS UCS
WHERE UC.TABLE_NAME = UCS.TABLE_NAME
AND UC.CONSTRAINT_NAME = UCS.CONSTRAINT_NAME
AND UC.CONSTRAINT_TYPE = 'P'
)CS
WHERE UT.TABLE_NAME = CS.TABLE_NAME(+)
ORDER BY TABLE_NAME,1 ASC;
SELECT NULL "COLUMN_ID",
NULL "COLUMN_NAME",
NULL "COLUMN_TYPE",
NULL "LENGTH",
NULL "DATA_DEFAULT",
NULL "NULL",
NULL "PK",
TABLE_NAME
FROM USER_TAB_COLUMNS
UNION
SELECT COLUMN_ID,
UT.COLUMN_NAME,
DATA_TYPE,
CHAR_LENGTH,
LONG_TO_VARCHAR2(UT.TABLE_NAME,
UT.COLUMN_NAME,
'DATA_DEFAULT',
'USER_TAB_COLUMNS',
USER) "DATA_DEFAULT",
NULLABLE,
DECODE(UT.COLUMN_NAME, CS.COLUMN_NAME, 'Y', 'N') "IS_PAR_KEY",
UT.TABLE_NAME
FROM USER_TAB_COLUMNS UT,
(SELECT UC.TABLE_NAME, UCS.COLUMN_NAME
FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCS
WHERE UC.TABLE_NAME = UCS.TABLE_NAME
AND UC.CONSTRAINT_NAME = UCS.CONSTRAINT_NAME
AND UC.CONSTRAINT_TYPE = 'P') CS
WHERE UT.TABLE_NAME = CS.TABLE_NAME(+)
AND UT.COLUMN_NAME = CS.COLUMN_NAME(+)
ORDER BY TABLE_NAME, 1 ASC;