oracle查看該使用者的所有表名字、表注釋、欄位名、欄位注釋、是否為空白、欄位類型,oracle欄位
--oracle查看該使用者的所有表名字、表注釋、欄位名、欄位注釋、是否為空白、欄位類型select distinct TABLE_COLUMN.*, TABLE_NALLABLE.DATA_TYPE, TABLE_NALLABLE.NULLABLE from (select distinct utc.table_name table_name, utc.comments table_comments, ucc.column_name column_name, ucc.comments column_comments from user_tab_comments utc, user_col_comments ucc where utc.table_name = ucc.table_name and utc.table_name not like '%_B' and utc.table_name not like '%_Z' and utc.table_name not like '%1%') TABLE_COLUMN, (select distinct table_name, column_name, nullable, DATA_TYPE from user_tab_cols where table_name not like '%_B' and table_name not like '%_Z' and table_name not like '%1%') TABLE_NALLABLE where TABLE_COLUMN.column_name = TABLE_NALLABLE.column_name and TABLE_COLUMN.TABLE_NAME = TABLE_NALLABLE.table_name --and TABLE_COLUMN.column_comments like '%分類名稱%' and TABLE_COLUMN.table_comments like '%字典%';--多次去掉笛卡爾
怎將oracle表中的欄位類型、欄位注釋說明、欄位名一起查詢出來
樓主可以使用下面的語句:
SELECT b.column_name column_name --欄位名
,b.data_type data_type --欄位類型
,b.data_length --欄位長度
,a.comments comments --欄位注釋
FROM user_col_comments a
,all_tab_columns b
WHERE a.table_name = b.table_name and
a.table_name = 'table_name';
PS:
table_name 大小寫敏感。
在oracle中查詢已知表名的表中所有欄位名,每個欄位是否是主鍵,是否是外鍵,是否為空白的sql語句,
查詢表的所有列及其屬性:
select t.*,c.COMMENTS
from user_tab_columns t,user_col_comments c
where t.table_name = c.table_name and t.column_name = c.column_name and t.table_name = women;
尋找表的主鍵:
select cu.*
from user_cons_columns cu, user_constraints au
where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = women;
尋找表的外鍵(包括名稱,參考資料表的表名和對應的鍵名,下面是分成多步查詢):
select * from user_constraints c where c.constraint_type = 'R' and c.table_name = women
查詢外鍵約束的列名:
select * from user_cons_columns cl where cl.constraint_name = 外鍵名稱;
查詢參考資料表的鍵的列名:
select * from user_cons_columns cl where cl.constraint_name = 外鍵參考資料表的鍵名;