MySQL / Oracle 导出当前用户的表结构

最新推荐文章于 2025-02-05 13:53:36 发布

程序员的冷浪漫

最新推荐文章于 2025-02-05 13:53:36 发布

阅读量5.5k

点赞数 3

CC 4.0 BY-SA版权

分类专栏： SQL学习

本文链接：https://blog.youkuaiyun.com/sunquana/article/details/90667644

SQL学习专栏收录该内容

8 篇文章

订阅专栏

本文介绍了如何在MySQL和Oracle数据库中导出当前用户的表结构。在MySQL中，主要涉及查询建表语句；而在Oracle中，可以通过查询user_tab_columns, user_col_comments, user_tab_comments等系统表获取信息，或者使用all_系列的系统表进行全库或特定用户查询。此外，还提到了Oracle中匿名块用于修改索引的方法，但需要注意表名需大写。" 49385159,1274519,解决SearchView搜索时出现黑框问题,"['Android开发', 'UI交互', '搜索功能']

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1 MySQL

SELECT  CONCAT(column_comment, "(", column_name, ")")
FROM information_schema.columns
WHERE table_schema ='my_db'
 AND table_name = 'my_table'
 ORDER BY ordinal_position;

查询建表语句

SHOW CREATE TABLE `dbname`.`t_product_position`;

2 Oracle 导出当前用户的表结构

通过查询当前用户的 user_tab_columns，user_col_comments， user_tab_comments 来获取表结构。

select  A.TABLE_NAME as "表名",
        C.COMMENTS       AS "表说明",
        A.COLUMN_ID      AS "字段序号",
        A.COLUMN_NAME    AS "字段名",
        B.COMMENTS       AS "字段说明",
        A.DATA_TYPE      AS "字段数据类型",
        A.DATA_LENGTH    AS "数据长度",
        A.DATA_PRECISION AS "整数位",
        A.DATA_SCALE     AS "小数位"
 from USER_TAB_COLUMNS A left join user_col_comments B
 on A.TABLE_NAME = B.table_name and A.COLUMN_NAME = B.column_name 
 left join USER_TAB_COMMENTS C 
 on A.TABLE_NAME = C.table_name
 order by A.TABLE_NAME, A.COLUMN_ID

查询表名，以及表的注释

select C.TABLE_NAME as "表名", C.comments as "表说明", C.table_type 
from USER_TAB_COMMENTS C
-- where C.table_type ='TABLE'
order by C.table_type , C.TABLE_NAME

方法二
另外一种就是查询 all_tab_columns，all_col_comments， all_tab_comments ，查询所有表或者指定某个用户的表信息。
因为是查询所有的用户表，方法二查询会慢一些。

select  A.TABLE_NAME as "表名",
       C.COMMENTS       AS "表说明",
       A.COLUMN_ID      AS "字段序号",
       A.COLUMN_NAME    AS "字段名",
       B.COMMENTS       AS "字段说明",
       A.DATA_TYPE      AS "字段数据类型",
       A.DATA_LENGTH    AS "数据长度",
       A.DATA_PRECISION AS "整数位",
       A.DATA_SCALE     AS "小数位"
from ALL_TAB_COLUMNS A left join ALL_col_comments B
on A.TABLE_NAME = B.table_name and A.COLUMN_NAME = B.column_name and A.OWNER = B.OWNER
left join ALL_TAB_COMMENTS C  
on A.TABLE_NAME = C.table_name and A.OWNER = C.OWNER
 where A.owner = 'USER'

查询Oracle 建表语句，注意表名必须大写

select dbms_metadata.get_ddl('TABLE','COMMUNITY') from dual;

3 oracle 匿名块修改索引

支持重复执行

第一处
  isExistIndex number(10);
  indexName VARCHAR2(30);
  
第二处 
  unique (trade_date,product_id,position_direction,market,stock_code,flow_type),

第三处
  -- 更改索引,先查询索引的名称，然后删除索引，最后创建索引
  select count(*) into isExistIndex from user_ind_columns where table_name= UPPER('t_product_position') and column_name = upper('FLOW_TYPE');
  if isExistIndex <= 0 then
    select INDEX_NAME into indexName from user_ind_columns where table_name= UPPER('t_product_position') and column_name = upper('TRADE_DATE');
    if indexName is not NULL then
      execute immediate 'ALTER TABLE T_PRODUCT_POSITION DROP CONSTRAINT ' || indexName;
      execute immediate 'ALTER TABLE T_PRODUCT_POSITION ADD CONSTRAINT '|| indexName ||' UNIQUE ("TRADE_DATE", "PRODUCT_ID", "POSITION_DIRECTION", "MARKET", "STOCK_CODE", "FLOW_TYPE")';
    end if;
  end if;