-- Oracle查看用户权限下的所有表结构:
SELECT USER_TAB_COLS.TABLE_NAME as 表名,
USER_TAB_COLS.COLUMN_NAME as 字段名 ,
USER_TAB_COLS.DATA_TYPE as 字段类型,
user_col_comments.comments as 字段说明,
case
when USER_TAB_COLS.COLUMN_NAME IN (
SELECT col.column_name FROM user_constraints con, user_cons_columns col where con.constraint_name = col.constraint_name and con.constraint_type='P'
and col.column_name = USER_TAB_COLS.COLUMN_NAME) THEN 'True'
ELSE 'False'
END
as 主键,
USER_TAB_COLS.DATA_LENGTH as 占用字节数,
USER_TAB_COLS.DATA_PRECISION as 字段长度,
USER_TAB_COLS.DATA_SCALE as 小数位,
case when USER_TAB_COLS.NULLABLE = 'Y'Then 'True' else 'False' end as 可为空,
USER_TAB_COLS.DATA_DEFAULT AS 默认值,
T.created as 创建时间,
T.last_ddl_time 更改时间,
'False' as 自增长,
USER_TAB_COLS.Segment_Column_Id as 顺序,
case
when USER_TAB_COLS.COLUMN_NAME in(
SELECT indx.column_name FROM user_indexes indx1, user_ind_columns indx where indx1.index_name = indx.index_name and indx1.uniqueness='UNIQUE'
and indx.column_name = USER_TAB_COLS.COLUMN_NAME)
THEN 'True'
ELSE 'False'
END
as 是否聚集索引,
user_col_comments.comments as 备注
FROM user_objects T inner join USER_TAB_COLS on T.OBJECT_NAME = USER_TAB_COLS.TABLE_NAME inner join user_col_comments
on user_col_comments.TABLE_NAME = USER_TAB_COLS.TABLE_NAME and user_col_comments.COLUMN_NAME=USER_TAB_COLS.COLUMN_NAME
where T.OBJECT_TYPE='TABLE'
ORDER BY 表名,顺序
;
Oracle查看用户权限下的所有表结构
于 2025-02-05 13:53:36 首次发布