Oracle查看表结构

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