【備忘】Oracle常用系統資料表（做代碼產生器用得到）

select * from user_objects where object_type='TABLE';

select * from all_tables where owner='KH';

select * from dba_users; select * from user_all_tables;

select name,dbid from v$database;

--索引相關

select * from dba_indexes where owner='KH'; --索引，包括主鍵索引 select * from dba_ind_columns where index_owner='KH';--索引列 select i.index_name,i.uniqueness,c.column_name from dba_indexes i,dba_ind_columns c where i.index_name=c.index_name and owner='KH' and i.table_name ='BASE_DRIVER';--聯結使用

select * from dba_tables where owner='KH'; select * from dba_tab_comments where owner='KH';

--擷取表列表（包含表注釋）

select t.OWNER,t.TABLE_NAME,t.NUM_ROWS,c.TABLE_TYPE,c.COMMENTS from dba_tables t left join dba_tab_comments c on t.TABLE_NAME = c.TABLE_NAME where t.owner='KH';

select * from dba_constraints where OWNER='KH' and CONSTRAINT_TYPE='P'; select * from dba_cons_columns where  OWNER='KH'

--擷取表的主鍵列

select c.owner,c.constraint_name,c.table_name,cc.column_name,cc.position from dba_constraints c inner join dba_cons_columns cc on c.constraint_name = cc.constraint_name

where c.owner='KH' and c.constraint_type='P' and c.table_name='BASE_DRIVER';

select * from user_tab_columns where table_name='BASE_DRIVER' order by column_id; select * from dba_tab_cols where owner='KH' and table_name='BASE_DRIVER' order by column_id; select * from dba_tab_columns where owner='KH' and table_name='BASE_DRIVER' order by column_id; select * from dba_col_comments where owner='KH';

--擷取表的資料列（包含列注釋） c.DATA_LENGTH 資料長度，如果是NChar，NVarchar長度為定義長度的2倍，c.CHAR_COL_DECL_LENGTH,c.CHAR_LENGTH列定義長度

select c.COLUMN_ID,c.COLUMN_NAME,c.DATA_TYPE,c.DATA_LENGTH,c.DATA_PRECISION,c.DATA_SCALE,c.NULLABLE,c.DATA_DEFAULT,c.CHAR_COL_DECL_LENGTH,c.CHAR_LENGTH,cc.COMMENTS from dba_tab_columns c left join dba_col_comments cc on c.table_name=cc.table_name and c.column_name=cc.column_name where c.owner='KH' and c.table_name='BASE_DRIVER'order by c.column_id;

--擷取序列

select * from dba_objects where owner='KH' and object_type='SEQUENCE';

--擷取視圖

select * from dba_views where owner='KH'

--擷取預存程序

select * from dba_procedures where owner='KH'

select * from dba_source where type='PROCEDURE' and owner='KH' and name='' order by line;

--擷取預存程序參數

select * from all_arguments where owner='KH' and package_name=''

待續。。。