目的：通过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')