Oracle使用——PLSQL查询表结构并导出EXCEL

时间:2024-03-04 08:24:13

背景

  • 有一次需要查询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