Oracle查询数据库表和字段

查询数据库表

• 表名
• 表注释

SELECT t.TABLE_NAME tableName, tc.COMMENTS tableComment
FROM user_tables t
LEFT JOIN user_tab_comments tc ON t.TABLE_NAME = tc.TABLE_NAME
ORDER BY t.TABLE_NAME

查询数据表字段

• 表名
• 字段名
• 字段注释
• 数据类型
• 字段长度
• 主键
• 非空

SELECT tc.TABLE_NAME tableName, tc.COLUMN_NAME columnName, comme.COMMENTS columnComment, tc.DATA_TYPE columnType, tc.DATA_LENGTH dataLength,
CASE WHEN cons.CONSTRAINT_TYPE = 'P' THEN 1 ELSE 0 END AS columnPk,
CASE WHEN tc.NULLABLE = 'N' THEN 1 ELSE 0 END AS columnNotNull
FROM user_tab_columns tc
LEFT JOIN user_col_comments comme ON tc.TABLE_NAME = comme.TABLE_NAME AND tc.COLUMN_NAME = comme.COLUMN_NAME
LEFT JOIN user_cons_columns concol ON tc.TABLE_NAME = concol.TABLE_NAME AND tc.COLUMN_NAME = concol.COLUMN_NAME
LEFT JOIN user_constraints cons ON concol.CONSTRAINT_NAME = cons.CONSTRAINT_NAME
ORDER BY tc.COLUMN_ID