查询 oracle 数据库字段信息

查询 oracle 数据库字段信息

常用系统视图

Oracle 提供了多个系统视图用于查询表结构信息：

• USER_TAB_COLUMNS - 当前用户拥有的表的列信息
• ALL_TAB_COLUMNS - 当前用户有权限访问的表的列信息
• DBA_TAB_COLUMNS - 数据库中所有表的列信息（需要DBA权限）

基本查询语句

查询单个表的字段信息

SELECT 
    column_name, 
    data_type, 
    data_length, 
    nullable, 
    data_default,
    column_id
FROM 
    user_tab_columns
WHERE 
    table_name = '表名'
ORDER BY 
    column_id;
 

查询多个表的字段信息

SELECT 
    table_name,
    column_name, 
    data_type, 
    data_length
FROM 
    user_tab_columns
WHERE 
    table_name IN ('表1', '表2')
ORDER BY 
    table_name, 
    column_id;

高级查询选项

1. 包含注释信息

SELECT 
    c.column_name, 
    c.data_type, 
    c.data_length, 
    c.nullable,
    com.comments
FROM 
    user_tab_columns c
LEFT JOIN 
    user_col_comments com ON c.table_name = com.table_name AND c.column_name = com.column_name
WHERE 
    c.table_name = '表名'
ORDER BY 
    c.column_id;

2. 查询主键字段

SELECT 
    cols.column_name
FROM 
    user_constraints cons, 
    user_cons_columns cols
WHERE 
    cons.constraint_type = 'P'
    AND cons.constraint_name = cols.constraint_name
    AND cons.table_name = '表名';

3. 查询外键关系

SELECT 
    a.table_name, 
    a.column_name, 
    c_pk.table_name r_table_name, 
    c_pk.column_name r_column_name
FROM 
    user_cons_columns a
JOIN 
    user_constraints c ON a.constraint_name = c.constraint_name
JOIN 
    user_constraints c_pk ON c.r_constraint_name = c_pk.constraint_name
WHERE 
    c.constraint_type = 'R'
    AND a.table_name = '表名';

实用查询示例

查询特定模式下表的字段名类型长度

SELECT 
	atc.column_name,
	atc.data_type,
	atc.data_length,
	acc.comments
from
	all_tab_columns atc
left join all_col_comments acc on atc.owner = acc.owner and atc.table_name  = acc.table_name  and atc.column_name = acc.column_name
where
	acc.owner = '模式名' and acc.table_name  = '表名'

查询特定模式下的所有表结构

SELECT 
    t.table_name, 
    c.column_name, 
    c.data_type, 
    c.data_length, 
    c.nullable
FROM 
    all_tables t
JOIN 
    all_tab_columns c ON t.table_name = c.table_name AND t.owner = c.owner
WHERE 
    t.owner = '模式名'
ORDER BY 
    t.table_name, 
    c.column_id;

查询包含特定字段名的表

SELECT 
    table_name, 
    column_name
FROM 
    all_tab_columns
WHERE 
    column_name LIKE '%字段名%';

查询大字段类型(LOB)列

SELECT 
    table_name, 
    column_name, 
    data_type
FROM 
    user_tab_columns
WHERE 
    data_type IN ('CLOB', 'BLOB', 'NCLOB', 'BFILE');

	注意事项
	表名在查询中通常需要大写，除非创建时使用了引号强制小写
	对于DBA视图查询需要相应权限
	在大型数据库中查询所有表结构可能会影响性能
	可以使用DESCRIBE命令快速查看表结构：DESC 表名