1.只查看某個特定表的字段名
SELECT column_name, data_type, data_length
FROM user_tab_columns
WHERE table_name = '你的表名' -- 注意大寫
ORDER BY column_id;
2.查看當前用戶下所有表的字段名
SELECT table_name, column_name, data_type, data_length
FROM user_tab_columns
ORDER BY table_name, column_id;
說明：
table_name: 表名
column_name: 字段名
data_type: 數據類型（如 VARCHAR2, NUMBER）
data_length: 數據長度
若需要格式化字段展示，可使用如下SQL：
SELECT table_name || '.' || column_name AS full_column_name,data_type || '(' || data_length || ')' AS data_type_length
FROM user_tab_columns
ORDER BY table_name, column_id;
3.查看所有用戶的表字段名（你有權限訪問的）
SELECT owner, table_name, column_name, data_type, data_length
FROM all_tab_columns
ORDER BY owner, table_name, column_id;
4.查看數據庫中所有表的字段名（需要 DBA 權限）
SELECT owner, table_name, column_name, data_type, data_length
FROM dba_tab_columns
ORDER BY owner, table_name, column_id;
5.查看當前用戶下字段名、數據類型及長度
SELECT table_name,column_name,-- 格式化數據類型 + 長度/精度CASE WHEN data_type IN ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2') THEN data_type || '(' || CASE char_used WHEN 'C' THEN char_length || ' CHAR'WHEN 'B' THEN data_length || ' BYTE'END || ')'WHEN data_type = 'NUMBER' THEN CASE WHEN data_precision IS NULL THEN 'NUMBER' WHEN data_scale = 0 THEN 'NUMBER(' || data_precision || ')' ELSE 'NUMBER(' || data_precision || ',' || data_scale || ')'ENDELSE data_typeEND AS formatted_data_type,-- 實際存儲長度（以字節為單位）data_length AS actual_length_bytesFROM user_tab_columns
ORDER BY table_name, column_id;若需格式化顯示字段長度，可使用如下SQL:
SELECT table_name,column_name,-- 格式化數據類型（去掉 BYTE/CHAR）CASE WHEN data_type IN ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2') THEN data_type || '(' || CASE char_used WHEN 'C' THEN char_lengthWHEN 'B' THEN data_lengthEND || ')'WHEN data_type = 'NUMBER' THEN CASE WHEN data_precision IS NULL THEN 'NUMBER' WHEN data_scale = 0 THEN 'NUMBER(' || data_precision || ')' ELSE 'NUMBER(' || data_precision || ',' || data_scale || ')'ENDELSE data_typeEND AS formatted_data_type,-- 實際字節長度（定義層面上的）data_length AS actual_length_bytesFROM user_tab_columns
ORDER BY table_name, column_id;
6.查詢前用戶下所有表的字段名稱、字段長度、是否允許為空
SELECT table_name,column_name,data_type,CASE WHEN data_type IN ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2') THENchar_lengthWHEN data_type = 'NUMBER' THENdata_precisionELSEdata_lengthEND AS length,CASE WHEN nullable = 'Y' THEN '是'ELSE '否'END AS is_nullable
FROM user_tab_columns
ORDER BY table_name, column_id;
若需格式化顯示長度，可使用如下SQL:
SELECT table_name,column_name,CASE WHEN data_type IN ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2') THEN data_type || '(' || CASE char_used WHEN 'C' THEN char_length WHEN 'B' THEN data_length ELSE data_length END || ')'WHEN data_type = 'NUMBER' THEN CASE WHEN data_precision IS NULL THEN 'NUMBER'WHEN data_scale = 0 THEN 'NUMBER(' || data_precision || ')'ELSE 'NUMBER(' || data_precision || ',' || data_scale || ')'ENDELSE data_typeEND AS data_type_length,CASE WHEN nullable = 'Y' THEN '是'ELSE '否'END AS is_nullable
FROM user_tab_columns
ORDER BY table_name, column_id;
)