<!-- /* Font Definitions */ @font-face{font-family:宋体;panose-1:2 1 6 0 3 1 1 1 1 1;mso-font-alt:SimSun;mso-font-charset:134;mso-generic-font-family:auto;mso-font-pitch:variable;mso-font-signature:3 135135232 16 0 262145 0;}@font-face{font-family:"Cambria Math";panose-1:2 4 5 3 5 4 6 3 2 4;mso-font-charset:1;mso-generic-font-family:roman;mso-font-format:other;mso-font-pitch:variable;mso-font-signature:0 0 0 0 0 0;}@font-face{font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;mso-font-charset:0;mso-generic-font-family:swiss;mso-font-pitch:variable;mso-font-signature:-1610611985 1073750139 0 0 159 0;}@font-face{font-family:"/@宋体";panose-1:2 1 6 0 3 1 1 1 1 1;mso-font-charset:134;mso-generic-font-family:auto;mso-font-pitch:variable;mso-font-signature:3 135135232 16 0 262145 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal{mso-style-unhide:no;mso-style-qformat:yes;mso-style-parent:"";margin:0cm;margin-bottom:.0001pt;text-align:justify;text-justify:inter-ideograph;text-indent:10.0pt;mso-char-indent-count:2.0;mso-pagination:none;font-size:10.5pt;mso-bidi-font-size:11.0pt;font-family:"Calibri","sans-serif";mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;mso-bidi-font-family:"Times New Roman";mso-bidi-theme-font:minor-bidi;mso-font-kerning:1.0pt;}p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph{mso-style-priority:34;mso-style-unhide:no;mso-style-qformat:yes;margin:0cm;margin-bottom:.0001pt;text-align:justify;text-justify:inter-ideograph;text-indent:21.0pt;mso-char-indent-count:2.0;mso-pagination:none;font-size:10.5pt;mso-bidi-font-size:11.0pt;font-family:"Calibri","sans-serif";mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-fareast-font-family:宋体;mso-fareast-theme-font:minor-fareast;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;mso-bidi-font-family:"Times New Roman";mso-bidi-theme-font:minor-bidi;mso-font-kerning:1.0pt;}.MsoChpDefault{mso-style-type:export-only;mso-default-props:yes;mso-bidi-font-family:"Times New Roman";mso-bidi-theme-font:minor-bidi;}.MsoPapDefault{mso-style-type:export-only;text-indent:10.0pt;mso-char-indent-count:2.0;} /* Page Definitions */ @page{mso-page-border-surround-header:no;mso-page-border-surround-footer:no;}@page Section1{size:612.0pt 792.0pt;margin:72.0pt 90.0pt 72.0pt 90.0pt;mso-header-margin:36.0pt;mso-footer-margin:36.0pt;mso-paper-source:0;}div.Section1{page:Section1;} /* List Definitions */ @list l0{mso-list-id:869949803;mso-list-type:hybrid;mso-list-template-ids:436121502 -1841129410 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;}@list l0:level1{mso-level-text:%1);mso-level-tab-stop:none;mso-level-number-position:left;margin-left:39.0pt;text-indent:-18.0pt;mso-ansi-font-weight:bold;}@list l1{mso-list-id:1828283470;mso-list-type:hybrid;mso-list-template-ids:-1727213148 2001394462 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;}@list l1:level1{mso-level-text:%1);mso-level-tab-stop:none;mso-level-number-position:left;margin-left:18.0pt;text-indent:-18.0pt;}ol{margin-bottom:0cm;}ul{margin-bottom:0cm;}-->
导出Oracle数据库中所所有用户表的表结构
问题描述:
将Oracle数据库中所有用户表的表结构导出,并存储到Excel表中,存储信息如下图所示:
解决方案:
1) 启动PL/SQL,应用需要导出表结构的用户登录目标数据库实例
2) New-SQLwindow创建查询窗口
3) 在查询窗口中输入以下查询语句:
select t.TABLE_NAME 表名,t.COLUMN_ID 序号,t.COLUMN_NAME 字段名,t.DATA_TYPE 类型,t. DATA_LENGTH 长度,t.NULLABLE 是否为空
from user_tab_columns t
其中User_tab_columns为当前用户表结构信息表。
4) 只想完成后,在查询窗口中点击 按钮显示所有记录信息
5) 右键-copy to Excel,将查询信息保存到Excel中
6) 通过Excel中打开保存的查询结果数据,选择数据-筛选,选择不同的表,然后可以取出不同表的表结构
7) 如果在查询过程中选择特定表的表结构则执行以下语句
select t.TABLE_NAME 表名,t.COLUMN_ID 序号,t.COLUMN_NAME 字段名,t.DATA_TYPE 类型,t. DATA_LENGTH 长度,t.NULLABLE 是否为空
from user_tab_columns t
where TABLE_NAME=’表名’
注:这里的表名,需要查询的表名替换,比如User表则写成:where TABLE_NAME=’User’
经验总结:
1) 数据表User_tab_columns中存储了当前用户所有数据表的表结构。
2) 数据表all_tab_columns中存储了当前数据库实例中所有数据表的表结构。
3) 数据表all_tab_columns 和数据表user_tab_columns的区别在于表all_tab_columns多了一个owner字段,用来存储表所属的用户。
通过 PL/SQL 可以将查询结果直接导出到 Excel 文件中。