oracle查看所有对象（表、视图、索引、函数、存储过程等）的详细参数和注释，DDL定义语句

目录

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