查询oracle相关表结构

来源：互联网发布：淘宝搜索不到东西编辑：程序博客网时间：2024/05/14 00:53

--查询索引

SELECT
T.INDEX_NAME,
LISTAGG(t.column_name,',') WITHIN GROUP (ORDER BY t.index_name)

FROM USER_IND_COLUMNS T, USER_INDEXES I
WHERE T.INDEX_NAME = I.INDEX_NAME
AND T.TABLE_NAME = I.TABLE_NAME
AND T.TABLE_NAME ='TT_DEPOSIT'
AND i.index_name NOT LIKE 'PK%'
GROUP BY t.index_name

--查询表结构信息，有复合主键的表

SELECT UTC.COLUMN_NAME,
DECODE(UTC.DATA_TYPE,
'DATE',
UTC.DATA_TYPE,
'VARCHAR2',
UTC.DATA_TYPE || '(' || UTC.DATA_LENGTH || ')',
'NUMBER',
DECODE(UTC.DATA_SCALE,
0,
UTC.DATA_TYPE || '(' || UTC.DATA_PRECISION || ')',
UTC.DATA_TYPE || '(' || UTC.DATA_PRECISION || ',' ||
UTC.DATA_SCALE || ')')),

UTC.NULLABLE,
UTC.DATA_DEFAULT,

DECODE((SELECT R.COLUMN_NAME
FROM USER_CONSTRAINTS CON,

(SELECT *
FROM USER_CONS_COLUMNS COL
WHERE COL.CONSTRAINT_NAME LIKE 'PK%') R
WHERE CON.TABLE_NAME = R.TABLE_NAME
AND r.column_name = utc.COLUMN_NAME
AND CON.CONSTRAINT_TYPE = 'P'
AND CON.TABLE_NAME = 'TT_DEPOSIT'),
UTC.COLUMN_NAME,
'Y',
''),
UCC.COMMENTS
FROM USER_TAB_COLUMNS UTC, USER_COL_COMMENTS UCC
WHERE UTC.TABLE_NAME = UCC.TABLE_NAME
AND UTC.COLUMN_NAME = UCC.COLUMN_NAME

AND UTC.TABLE_NAME = 'TT_DEPOSIT'

ORDER BY COLUMN_ID

--查询表结构信息，只有一个主键的表

SELECT UTC.COLUMN_NAME,
DECODE(UTC.DATA_TYPE,
'DATE',
UTC.DATA_TYPE,
'VARCHAR2',
UTC.DATA_TYPE || '(' || UTC.DATA_LENGTH || ')',
'NUMBER',
DECODE(UTC.DATA_SCALE,
0,
UTC.DATA_TYPE || '(' || UTC.DATA_PRECISION || ')',
UTC.DATA_TYPE || '(' || UTC.DATA_PRECISION || ',' ||
UTC.DATA_SCALE || ')')),

UTC.NULLABLE,
UTC.DATA_DEFAULT,
decode(col.column_name, UTC.COLUMN_NAME,'Y',''),
UCC.COMMENTS
FROM USER_TAB_COLUMNS UTC, USER_COL_COMMENTS UCC,user_constraints con,user_cons_columns col
WHERE UTC.TABLE_NAME = UCC.TABLE_NAME
AND UTC.COLUMN_NAME = UCC.COLUMN_NAME
AND con.constraint_name=col.constraint_name and con.constraint_type='P'
and col.table_name= UCC.table_name
AND UTC.TABLE_NAME = 'TM_DEPARTMENT_CORP'
ORDER BY COLUMN_ID

--查询绑定变量

select * from v$sql_bind_capture t where t.sql_id='6h8qtssjkn5xd' order by t.LAST_CAPTURED desc

0 0