Oracle使用——PLSQL查询表结构并导出EXCEL
背景
- 有一次需要查询Oracle数据库中的所有表接口并且导出excel,方法记录如下
使用
- 使用PLSQL工具查询表结构,SQL语句如下
SELECT B.TABLE_NAME AS '表名', C.COMMENTS AS '表说明', B.COLUMN_ID AS '字段序号', B.COLUMN_NAME AS '字段名', B.DATA_TYPE AS '字段数据类型', B.DATA_LENGTH AS '数据长度', B.DATA_PRECISION AS '整数位', B.DATA_SCALE AS '小数位', A.COMMENTS AS '字段说明' FROM ALL_COL_COMMENTS A, ALL_TAB_COLUMNS B, ALL_TAB_COMMENTS C WHERE A.TABLE_NAME IN (SELECT U.TABLE_NAME FROM USER_ALL_TABLES U) AND A.OWNER = B.OWNER AND A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME AND C.TABLE_NAME = A.TABLE_NAME AND C.OWNER = A.OWNER AND A.OWNER = 'xxx_db_name' --其中xxx_db_name为DB用户名 AND A.TABLE_NAME like '%xxx_table_name%' --xxx_table_name为表名称 ORDER BY A.TABLE_NAME, B.COLUMN_ID;
- 查询结果如下
- 导出excel即可
- Next