目录
1.总结
1.1.查看所有对象的详细信息和注释（表、视图、函数等）
1.2.查看所有对象DDL定义语句（表、视图、函数等）
2.测试
2.1.查看所有对象的详细信息和注释（表、视图、函数等）
2.1.1.表、视图、字段的详细参数
2.1.2.表、视图、字段的注释说明
2.1.3.函数、存储过程、触发器等
2.1.4.对象(表、视图、索引、函数等40类object_type)
2.1.5.更多信息见SYS（索引、函数、触发器等）
2.1.5.1.比如查看索引
2.2.查看所有对象DDL定义语句（表、视图、函数等）
9.相关链接
9.1.mysql 查看建表语句DDL，简要查看表字段信息，详细查看表字段信息。SHOW CREATE TABLE sys_user；DESCRIBE sys_user；_mysql
9.2.MySQL、Oracle 查看表占用空间大小
1.总结
1.1.查看所有对象的详细信息和注释（表、视图、函数等）
按照范围分为：dba ⊇ all ⊇ user（即 数据库管理员 ⊇ 所有用户 ⊇ 当前登录用户）dba、all 比 user 多一个 owner 字段
-- 1.DBA
-- 1.1.表、视图、字段的详细信息
-- 表、视图
select * from sys.dba_tables where owner in ('TEST') order by owner,table_name;
select * from sys.dba_views where owner in ('TEST') order by owner,view_name; -- 在NBCB,select 了TEXT_VC字段导致where 不到任何数据，不知道原因
-- 字段
select * from sys.dba_tab_cols where owner in ('TEST') order by owner,table_name,column_id;
-- 1.2.表、视图、字段的注释说明
-- 表、视图
select * from sys.dba_tab_comments where owner in ('TEST') order by owner,table_type,table_name;
-- 字段
select * from sys.dba_col_comments where owner in ('TEST') order by owner,table_name,column_name;
-- 1.3.函数、存储过程、触发器等
select * from sys.dba_procedures where owner in ('TEST') order by owner,object_type,object_name;
-- 1.4.对象(表、视图、索引、函数等40类object_type)
select * from sys.dba_objects where owner in ('TEST') order by owner,object_type,object_name;
-- 2.所有用户
-- 2.1.表、视图、字段的详细信息
-- 表、视图
select * from sys.all_tables where owner in ('TEST') order by owner,table_name;
select * from sys.all_views where owner in ('TEST') order by owner,view_name; -- 在NBCB,select 了TEXT_VC字段导致where 不到任何数据，不知道原因
-- 字段
select * from sys.all_tab_cols where owner in ('TEST') order by owner,table_name,column_id;
-- 2.2.表、视图、字段的注释说明
-- 表、视图
select * from sys.all_tab_comments where owner in ('TEST') order by owner,table_type,table_name;
-- 字段
select * from sys.all_col_comments where owner in ('TEST') order by owner,table_name,column_name;
-- 2.3.函数、存储过程、触发器等
select * from sys.all_procedures where owner in ('TEST') order by owner,object_type,object_name;
-- 2.4.对象(表、视图、索引、函数等40类object_type)
select * from sys.all_objects where owner in ('TEST') order by owner,object_type,object_name;
-- 3.当前登录用户
-- 3.1.表、视图、字段的详细信息
-- 表、视图
select * from sys.user_tables order by table_name;
select * from sys.user_views order by view_name; -- 在NBCB,select 了TEXT_VC字段导致where 不到任何数据，不知道原因
-- 字段
select * from sys.user_tab_cols order by table_name,column_id;
-- 3.2.表、视图、字段的注释说明
-- 表、视图
select * from sys.user_tab_comments order by table_type,table_name;
-- 字段
select * from sys.user_col_comments order by table_name,column_name;
-- 3.3.函数、存储过程、触发器等
select * from sys.user_procedures order by object_type,object_name;
-- 3.4.对象(表、视图、索引、函数等40类object_type)
select * from sys.user_objects order by object_type,object_name;
1.2.查看所有对象DDL定义语句（表、视图、函数等）
-- 查看对象（表、视图、索引、函数、存储过程等等）DDL定义语句
-- 语法：select dbms_metadata.get_ddl({object_type}, {object_type}, {schema_name}) from dual;
select dbms_metadata.get_ddl('TABLE', 'STUDENT', 'TEST') from dual;
select dbms_metadata.get_ddl('VIEW', 'STUDENT_VIEW', 'TEST') from dual;
select dbms_metadata.get_ddl('INDEX', 'IDX_S_NAME', 'TEST') from dual;
select dbms_metadata.get_ddl('FUNCTION', 'GET_STU_NAME', 'TEST') from dual;
select dbms_metadata.get_ddl('PROCEDURE', 'MY_PROCEDURE', 'TEST') from dual;
2.测试
建表、视图、索引
-- 表
CREATE TABLE STUDENT
(
S_ID NUMBER (11,0),
S_NAME NVARCHAR2(100)
);
COMMENT ON TABLE STUDENT IS '学生';
COMMENT ON COLUMN STUDENT.S_ID IS '学生学号';
COMMENT ON COLUMN STUDENT.S_NAME IS '学生姓名';
-- 索引
CREATE INDEX IDX_S_NAME ON STUDENT (S_NAME ASC);
-- 视图
CREATE VIEW STUDENT_VIEW AS
select S_ID, S_NAME
from STUDENT
order by S_ID desc;
COMMENT ON TABLE STUDENT_VIEW IS '学生按学号倒序';
-- 函数
CREATE OR REPLACE function GET_STU_NAME(p_s_id number)
return varchar2 is p_s_name varchar2(100);
begin
select s_name into p_s_name from student where s_id = p_s_id;
return p_s_name;
end;
-- 存储过程
CREATE OR REPLACE procedure "MY_PROCEDURE" is
begin
commit;
end;
2.1.查看所有对象的详细信息和注释（表、视图、函数等）
2.1.1.表、视图、字段的详细参数
-- 1.1.表、视图、字段的详细参数
-- 表、视图
select * from sys.dba_tables where owner in ('TEST') order by owner,table_name;
select * from sys.dba_views where owner in ('TEST') order by owner,view_name; -- 在NBCB,select 了TEXT_VC字段导致where 不到任何数据，不知道原因
-- 字段
select * from sys.dba_tab_cols where owner in ('TEST') order by owner,table_name,column_id;
2.1.2.表、视图、字段的注释说明
-- 1.2.表、视图、字段的注释说明
-- 表、视图
select * from sys.dba_tab_comments where owner in ('TEST') order by owner,table_type,table_name;
-- 字段
select * from sys.dba_col_comments where owner in ('TEST') order by owner,table_name,column_name;
2.1.3.函数、存储过程、触发器等
-- 1.3.函数、存储过程、触发器等
select * from sys.dba_procedures where owner in ('TEST') order by owner,object_type,object_name;
2.1.4.对象(表、视图、索引、函数等40类object_type)
-- 1.4.对象(表、视图、索引、函数等40类object_type)
select * from sys.dba_objects where owner in ('TEST') order by owner,object_type,object_name;
2.1.5.更多信息见SYS（索引、函数、触发器等）
本文所有数据来自SYS，更多信息见SYS
2.1.5.1.比如查看索引
select distinct object_type from sys.dba_objects order by object_type;
2.2.查看所有对象DDL定义语句（表、视图、函数等）
-- 查看对象（表、视图、索引、函数、存储过程等等）DDL定义语句
-- 语法：select dbms_metadata.get_ddl({object_type}, {object_type}, {schema_name}) from dual;
select dbms_metadata.get_ddl('TABLE', 'STUDENT', 'TEST') from dual;
select dbms_metadata.get_ddl('VIEW', 'STUDENT_VIEW', 'TEST') from dual;
select dbms_metadata.get_ddl('INDEX', 'IDX_S_NAME', 'TEST') from dual;
select dbms_metadata.get_ddl('FUNCTION', 'GET_STU_NAME', 'TEST') from dual;
select dbms_metadata.get_ddl('PROCEDURE', 'MY_PROCEDURE', 'TEST') from dual;
9.相关链接
9.1.mysql 查看建表语句DDL，简要查看表字段信息，详细查看表字段信息。SHOW CREATE TABLE sys_user；DESCRIBE sys_user；_mysql
mysql 查看建表语句DDL，简要查看表字段信息，详细查看表字段信息。SHOW CREATE TABLE sys_user；DESCRIBE sys_user；_mysql 查看表的ddl_茅坑的小石头的博客-CSDN博客mysql 查看建表语句DDL，简要查看表字段信息，详细查看表字段信息。SHOW CREATE TABLE sys_user；DESCRIBE sys_user；_mysql 查看表的ddlhttps://blog.csdn.net/lishuoboy/article/details/127903357
9.2.MySQL、Oracle 查看表占用空间大小
MySQL、Oracle 查看表占用空间大小_oracle 查询表占空间大小-CSDN博客文章浏览阅读10w+次。查看当前用户select SEGMENT_NAME as TABLE_NAME,BYTES/1024/1024 as 大小MB from USER_SEGMENTS where SEGMENT_TYPE='TABLE' order by BYTES desc;select * from USER_SEGMENTS order by BYTES desc;查看所有用户..._oracle 查询表占空间大小https://blog.csdn.net/lishuoboy/article/details/100119161