目的：通过SQL进行查看表结构，因为使用PL/SQL连接工具，连接到公司的数据库上经常断开。故改为使用Navicat连接数据库,个人觉得这个查看表结构很困难。
查看表结构和约束精简
-- 查询指定表的结构
WITH tmp AS (
SELECT
table_name,
comments
FROM
user_tab_comments
WHERE
table_name = "UPPER" ('table_name')
)
select
table_name column_name, -- 其实是表名
comments data_type, -- 其实是备注信息
'' nullbale,
'表，非列' comments
FROM
tmp
UNION ALL
SELECT
t1.column_name,
t1.data_type,
t1.nullable,
t2.comments
FROM
(
(
SELECT
column_name,
data_type || '(' || data_length || ')' data_type,
nullable
FROM
user_tab_columns
WHERE
table_name = (
SELECT
tmp.table_name
FROM
tmp
)
ORDER BY
column_id
) t1
LEFT JOIN (
SELECT
column_name,
comments
FROM
user_col_comments
WHERE
table_name = (
SELECT
tmp.table_name
FROM
tmp
)
) t2 ON t1.column_name = t2.column_name
);
-- 查询表的约束
select * from USER_CONSTRAINTS where table_name = UPPER ('table_name')
其他一些相关的SQL比较杂乱
-- 查询表
-- 查询当前用户的所有表
select * from user_tables;
-- 查询所有用户的表
select * from all_tables;
-- 包括系统表
select * from dba_tables;
-- 获取表字段 all_tab_columns dba_tab_columns;
select * from user_tab_columns;
-- 获取表注释
select * from user_tab_comments;
-- 查看表字段约束
select * from USER_CONSTRAINTS where table_name = UPPER ('table_name')
-- C (check constraint on a table) C 表示 CHECK 约束。
-- P (primary key) P 表示主键
-- U (unique key) U 表示唯一
-- R (referential integrity) P 表示引用(外键)
-- V (with check option, on a view)
-- O (with read only, on a view)
-- 获取字段注释
select * from user_col_comments;
-- 查看指定用户表的表备注以及字段以及备注
SELECT
comments
FROM
user_tab_comments
WHERE
table_name = "UPPER" ('table_name');
-- 指定用户表字段
SELECT
column_name,
data_type || '(' || data_length || ')' data_type,
nullable
FROM
user_tab_columns
WHERE
table_name = UPPER ('table_name')
ORDER BY
column_id;
select * from user_col_comments where table_name = UPPER ('table_name')