oracle查看表结构/视图结构、所有表、DBMS、字段等
-
文章目录
查看表结构(字段)查看表结构(字段) GET_DDL()方式查看视图结构 GET_DDL()方式
查看表结构(字段) 基于user_col_comments查看表结构(字段) 基于user_tab_columnsuser_tab_columns查看字段长度
查看所有表(有多少表)查看所有表(有多少表) all_tables查看所有表(有多少表) user_tables查看所有视图(有多少视图)
DBMS字段基本的字段常见的类型date、timestamp、timestamp(6)的区别timestamp的字段查看字段的sql-基础sql(主要用来拿元数据)查看字段的sql(实际用这个)
其他number类型的default为什么不能通过字符串拼接 报错 ORA-00932: 数据类型不一致: 应为 CHAR, 但却获得 LONG todo
查看表结构(字段)
这属于常规需求了，不止一种方法。
查看表结构(字段) GET_DDL()方式
SELECT DBMS_METADATA.GET_DDL('TABLE','T_USER') FROM DUAL;
需要注意的是，这几个都是大写： DBMS_METADATA.GET_DDL TABLE T_USER
返回结果是建表语句，类似于mysql的show create table。
查看视图结构 GET_DDL()方式
同上，把TABLE换成VIEW即可：
SELECT DBMS_METADATA.GET_DDL('VIEW','T_USER_VIEW') FROM DUAL;
查看表结构(字段) 基于user_col_comments
语句：
select * from user_col_comments where table_name like '%表名%';
这是一种简便的方式，输出只有3列(TABLE_NAME、COLUMN_NAME、COMMENTS)， 适用于快速查看表字段，对表有个了解。输入结果示例：
TABLE_NAMECOLUMN_NAMECOMMENTST_USERIDIDT_USERUSER_ACCOUNT账号
查看表结构(字段) 基于user_tab_columns
user_tab_columns的信息比较全，列也很多，这里只列几个常用的列。
user_tab_columns查看字段长度
注：需要有对应权限才可以。
SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH
FROM USER_TAB_COLUMNS 子
WHERE TABLE_NAME = 'MESSAGE_TEMPLATE_RESUME';
查看所有表(有多少表)
查看所有表(有多少表) all_tables
注意记得加owner即可。
这条sql有问题，数量多很多：
SELECT TABLE_NAME FROM all_tables 子;
先分组看下有哪些owner，肯定包含要找的用户及sys用户等：
SELECT owner FROM all_tables 子 group by owner;
带上具体的用户名就是对的了(用这条sql即可)：
SELECT TABLE_NAME FROM all_tables 子 where owner='用户名';
查看所有表(有多少表) user_tables
简单点就user_tables，直接就是当前用户的所有表。
select * from user_tables;
查看所有视图(有多少视图)
也是可以查看全部和当前用户的。
select * from all_views where owner='用户';
select * from user_views; # 查出来的就是当前用户的
DBMS
DBMS是database manage system(数据库管理系统)的简称。 以DBMS_开头的是一套基础方法，例如： DBMS_METADATA.GET_DDL() # 获取表结构 DBMS_RANDOM.STRING() # 生成随机值
字段
基本的字段
最起码得有这几个字段。
列名 AMOUNT 类型 DECIMAL 长度 22 标度 127 是否允许为空 Y/N # 这个字段和user_col_columns一致 注释 金额(字段名一般在注释里) 顺序 2
常见的类型
CHAR # VARCHAR2 # NUMBER # DATE # 这个需要特别注意下，date在数据库长度是7位(世纪年月日时分秒分别占一个字节) TIMESTAMP(6) # 这个就是timestamp
date、timestamp、timestamp(6)的区别
字段描述date精度小数点后3位timestamptimestamp类型默认就是6位timestamp(6)timestamp类型默认就是6位
有timestamp(6)，不由得的想，是否还有timestamp(n)? 确实如此，最多到timestamp(9)。
timestamp的字段
查看字段的sql-基础sql(主要用来拿元数据)
语句：
SELECT
t.column_name AS 列名,
t.data_type AS 类型,
t.data_length AS 长度,
t.char_length AS 字符长度, -- 只有varchar2、char等字符类型有
t.data_precision AS 标度,
t.data_scale AS data_scale,
t.nullable AS 是否允许为空,
t2.comments AS 注释,
t.column_id AS 顺序 -- 并不重要
from user_tab_columns t
inner JOIN
USER_COL_COMMENTS t2
ON t.column_name=t2.column_name
where t.table_name = '表名'
AND t2.table_name='表名';
注：where条件后为什么要写两个table_name呢，是因为不同表，id等字段都可能重复，如果不都加表名，id会大量重复(不同表间的id)。
还有一点，nullable(是否允许为空)是数据库直接查出来的，这个和我们常规的逻辑是有点逆着的，常规的逻辑是是否必填。这里还是推荐按字段的来，否则sql需要反向写。 是否必填 # 人类的思维 是否允许为空 # 数据库里的字段
查看字段的sql(实际用这个)
语句：
SELECT
t.TABLE_name AS 表名,
t.column_name AS 列名,
t.data_type
|| CASE
WHEN t.data_type ='VARCHAR2' THEN '('|| t.data_length ||')'
WHEN t.data_type ='CHAR' THEN '('|| t.data_length ||')'
WHEN t.data_type ='NUMBER'
and t.data_precision IS NOT NULL AND t.data_scale is NOT null
THEN '('|| t.data_precision || ',' || t.data_scale ||')'
--WHEN 'NUMBER' THEN
END
AS 类型及长度精度,
t.nullable AS 是否允许为空,
t2.comments AS 注释
from user_tab_columns t
inner JOIN
USER_COL_COMMENTS t2
ON t.column_name=t2.column_name
where t.table_name = '表名'
AND t2.table_name='表名'
这里加上table_name是因为迁库时往往是多张表，需要带上表名区分下。
其他
number类型的default为什么不能通过字符串拼接 报错 ORA-00932: 数据类型不一致: 应为 CHAR, 但却获得 LONG todo
错误信息： ORA-00932: 数据类型不一致: 应为 CHAR, 但却获得 LONG
好奇怪啊，直接查可以查出来，但是拼接就报错。 本来是计划，default信息也拼接到字段里呢，结果卡住了。