MySQL / Oracle 导出当前用户的表结构
原创
©著作权归作者所有:来自51CTO博客作者孙权a的原创作品,请联系作者获取转载授权,否则将追究法律责任
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;