Oracle一键查询用户所有表名，表注释，字段名，字段注释，数据类型，长度，是否为空，默认值，主键，获取之后基本能复制出库中所有表结构

最新推荐文章于 2024-03-27 02:07:09 发布

木及言风

最新推荐文章于 2024-03-27 02:07:09 发布

阅读量996

点赞数 2

文章标签： oracle 数据库 sql 数据库开发大数据

本文链接：https://blog.csdn.net/weixin_56496488/article/details/127669074

版权

先创建一个翻译默认值的函数



CREATE OR REPLACE FUNCTION ALL_TAB_COLUMNS_DATA_DEFAULT(
   in_owner      varchar2,
   in_table_name varchar2,
   in_column     varchar2
)
RETURN varchar AS
  text_c1 varchar2(32767);
  sql_cur varchar2(2000);
begin
 sql_cur := ' select data_default '||
             ' from all_tab_columns tc '||
             ' where 1=1 '||
             ' and tc.OWNER='''||in_owner||''' '||
             ' and tc.TABLE_NAME='''||in_table_name||''' '||
             ' and tc.COLUMN_NAME='''||in_column||''' '
             ;
 --dbms_output.put_line (sql_cur);
 execute immediate sql_cur into text_c1;
 text_c1 := substr(text_c1, 1, 4000);
 RETURN TEXT_C1; 
END;

执行下面这条语句就行


select a.table_name 表名,a.TABLE_COMMENTS 表注释,a.COLUMN_NAME 字段名,a.COLUMN_COMMENTS 字段注释,a.DATA_TYPE 数据类型,a.NULLABLE 是否能空,a.DATA_LENGTH 长度,b.MORENZHI 默认值,c.COLUMN_NAME 主键 from (
select distinct TABLE_COLUMN.*,
                TABLE_NALLABLE.DATA_TYPE,
                TABLE_NALLABLE.NULLABLE,DATA_LENGTH
  from (select distinct utc.table_name  table_name,
                        utc.comments    table_comments,
                        ucc.column_name column_name,
                        ucc.comments    column_comments
          from user_tab_comments utc, user_col_comments ucc
         where utc.table_name = ucc.table_name
          ) TABLE_COLUMN,
       (select distinct table_name, column_name, nullable, DATA_TYPE,DATA_LENGTH
          from user_tab_cols
         ) TABLE_NALLABLE
 where TABLE_COLUMN.column_name = TABLE_NALLABLE.column_name
   and TABLE_COLUMN.TABLE_NAME = TABLE_NALLABLE.table_name )a left join (
select --tc.OWNER,
       tc.TABLE_NAME,
       tc.COLUMN_NAME,
      -- tc.DATA_DEFAULT,
       ALL_TAB_COLUMNS_DATA_DEFAULT(tc.OWNER,tc.TABLE_NAME,tc.COLUMN_NAME) morenzhi
from all_tab_columns tc
where 1=1

and tc.DATA_DEFAULT is not null) b on a.table_name=b.table_name and a.COLUMN_NAME=b.COLUMN_NAME left join (select table_name ,COLUMN_NAME  from (
select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P')) c
on a.table_name=c.table_name and a.COLUMN_NAME=c.COLUMN_NAME--b=默认值c=主键
order by a.table_name

这是查出来的效果