其实我在网上看到一个高手把这段sql写出来了,不过没索引,所以我想问下索引怎么查。
网上的sql是这样的:
select A.COLUMN_NAME,
decode(A.DATA_TYPE,
'CHAR',
A.DATA_TYPE || '(' || A.DATA_LENGTH || ')',
'DATE',
A.DATA_TYPE,
'NUMBER',
A.DATA_TYPE,
'VARCHAR2',
A.DATA_TYPE || '(' || A.DATA_LENGTH || ')') as DATA_TYPE,
B.comments,
decode(C.COLUMN_NAME, null, 'No', 'Yes') as Key,
d.index_name,
decode(A.NULLABLE, 'N', 'NOT NULL', 'Y', 'NULL') as NULLABLE
from sys.user_tab_cols A,
sys.user_col_comments B,
(select col.column_name, c.table_name
from user_constraints c, user_cons_columns col
where c.constraint_name = col.constraint_name
and c.constraint_type = 'P') C
where upper(A.TABLE_NAME) = '表名'
and A.TABLE_NAME = B.table_name
and A.COLUMN_NAME = B.column_name
and A.Table_Name = C.TABLE_NAME(+)
and A.COLUMN_NAME = C.COLUMN_NAME(+)
and d.column_name=b.column_name我想在里边加条查询索引的sql脚本,我试了下这条sql语句:
select f.column_name, index_name
from user_indexes s, sys.user_tab_cols f
where s.table_name = f.table_name
and s.table_name = '表名'
这条sql语句不行,因为一个索引就对应了全部的列,N个索引的话就有N*列个数的行出来,汗!应该怎么做呢?
网上的sql是这样的:
select A.COLUMN_NAME,
decode(A.DATA_TYPE,
'CHAR',
A.DATA_TYPE || '(' || A.DATA_LENGTH || ')',
'DATE',
A.DATA_TYPE,
'NUMBER',
A.DATA_TYPE,
'VARCHAR2',
A.DATA_TYPE || '(' || A.DATA_LENGTH || ')') as DATA_TYPE,
B.comments,
decode(C.COLUMN_NAME, null, 'No', 'Yes') as Key,
d.index_name,
decode(A.NULLABLE, 'N', 'NOT NULL', 'Y', 'NULL') as NULLABLE
from sys.user_tab_cols A,
sys.user_col_comments B,
(select col.column_name, c.table_name
from user_constraints c, user_cons_columns col
where c.constraint_name = col.constraint_name
and c.constraint_type = 'P') C
where upper(A.TABLE_NAME) = '表名'
and A.TABLE_NAME = B.table_name
and A.COLUMN_NAME = B.column_name
and A.Table_Name = C.TABLE_NAME(+)
and A.COLUMN_NAME = C.COLUMN_NAME(+)
and d.column_name=b.column_name我想在里边加条查询索引的sql脚本,我试了下这条sql语句:
select f.column_name, index_name
from user_indexes s, sys.user_tab_cols f
where s.table_name = f.table_name
and s.table_name = '表名'
这条sql语句不行,因为一个索引就对应了全部的列,N个索引的话就有N*列个数的行出来,汗!应该怎么做呢?
WHERE S.table_name='EMP'
直接这样就可以了啊,还有其它什么需求?
oracle QQ群:54775466
期待您的一起探讨
欢迎爱好者入群学习
SELECT COLUMN_NAME, DATA_TYPE, COMMENTS, KEY, wm_concat(index_name) index_name, NULLABLE
FROM (SELECT A.COLUMN_NAME,
decode(A.DATA_TYPE,
'CHAR',
A.DATA_TYPE || '(' || A.DATA_LENGTH || ')',
'DATE',
A.DATA_TYPE,
'NUMBER',
A.DATA_TYPE,
'VARCHAR2',
A.DATA_TYPE || '(' || A.DATA_LENGTH || ')') AS DATA_TYPE,
B.comments,
decode(C.COLUMN_NAME, NULL, 'No', 'Yes') AS Key,
d.index_name,
decode(A.NULLABLE, 'N', 'NOT NULL', 'Y', 'NULL') AS NULLABLE
FROM sys.user_tab_cols A,
sys.user_col_comments B,
(SELECT col.column_name, c.table_name
FROM user_constraints c, user_cons_columns col
WHERE c.constraint_name = col.constraint_name AND
c.constraint_type = 'P') C,
user_ind_columns d
WHERE upper(A.TABLE_NAME) = '表名' AND
A.TABLE_NAME = B.table_name AND
A.COLUMN_NAME = B.column_name AND
A.Table_Name = C.TABLE_NAME(+) AND
A.COLUMN_NAME = C.COLUMN_NAME(+) AND
b.column_name = d.column_name(+))
GROUP BY COLUMN_NAME, DATA_TYPE, COMMENTS, KEY, NULLABLE;