qq_15966961的博客

该SQL可以直接导出oracle用户下的表结构信息，结果包含了表的字段基本信息，主键信息，以及字段备注信息等，经生产测试可用。sql脚本内容如下 --注：当前查询结果只包含 有主键字段的表结构信息 Select A.OWNER 用户, A.TABLE_NAME 表名, A.COLUMN_NAME 字段名, A.DATA_TYPE 数据类型, A.DATA_LENGTH 数据长度, A.NULLABLE 可否为空, case when( C.COLUMN_NAME = A.COLUMN_NAME and C.TABLE_NAME = A.TABLE_NAME) THEN 'Y' ELSE 'N' END as 是否主键, B.COMMENTS 备注 From all_tab_columns A, all_col_comments B, (select all_cons_columns.OWNER, all_cons_columns.TABLE_NAME, all_cons_columns.COLUMN_NAME from all_constraints, all_cons_columns where all_cons_columns.CONSTRAINT_NAME = all_constraints.CONSTRAINT_NAME and all_constraints.CONSTRAINT_TYPE = 'P' and all_constraints.OWNER = '用户名') C Where A.COLUMN_NAME = B.COLUMN_NAME And A.TABLE_NAME = B.TABLE_NAME And A.TABLE_NAME = C.TABLE_NAME And A.OWNER = C.OWNER And A.OWNER = B.OWNER And (A.OWNER = '用户名')