一、相关oracle涉及表属性信息的表
1、存储数据表信息
user_tables; //当前用户拥有的表
all_tables; //所有用户的表
dba_tables; //包括系统表
2、存储数据表注释信息
dba_tab_comments //包括系统表的
all_tab_comments //所有用户表
user_tab_comments //当前用户
3、存储数据表字段信息:
dba_col_columns(包括系统表字段)
all_col_columns ( 除系统表外字段 )
user_col_columns (当前用户表字段)
4、存储数据表字段注释信息:
dba_col_comments
all_col_comments
user_col_comments
5、表主键、约束信息
//1)约束的相关字段、表信息
dba_cons_columns
all_cons_columns
user_cons_columns
//2)主键和约束名称,及相关约束分类
dba_constraints
all_constraints
user_constraints
二、获取表相关字段属性的SQL
SELECT T.OWNER --用户名
,T.TABLE_NAME AS YWB --表名
,t2.comments AS YWBZS --表注释
,t.column_name AS ZDMC ---字段名称
,t3.comments AS ZDZS --字段注释
,CASE WHEN T4.COLUMN_NAME IS NOT NULL THEN '1' ELSE '0' END AS SFZJ --是否主键
,T.NULLABLE AS SFWK --是否为空
,T.DATA_TYPE AS ZDLX --字段类型
,T.DATA_LENGTH AS ZDCD --数据类型
FROM DBA_tab_columns t
--left join dba_tab_columns t1 on t.table_name=t1.table_name
LEFT JOIN DBA_tab_comments T2 ON t.table_name=t2.table_name AND T2.owner='SCOTT'
LEFT JOIN DBA_COL_comments T3 ON t.table_name=t3.table_name AND T.COLUMN_NAME=T3.COLUMN_NAME AND T3.owner='SCOTT'
left JOIN (SELECT A.OWNER,A.TABLE_NAME,A.COLUMN_NAME FROM dba_cons_columns A
INNER JOIN DBA_constraints B ON A.CONSTRAINT_NAME=B.CONSTRAINT_NAME AND A.OWNER=B.OWNER AND A.TABLE_NAME=B.TABLE_NAME
where A.owner='SCOTT' and B.CONSTRAINT_TYPE='P' --CONSTRAINT_TYPE='P'为主键约束
) T4 ON T.TABLE_NAME=T4.TABLE_NAME AND T.COLUMN_NAME=T4.COLUMN_NAME
WHERE T.OWNER='SCOTT' AND t2.table_type='TABLE'
ORDER BY T.TABLE_NAME,T.COLUMN_ID