文件名称：oracle sql of extracting table structure
文件大小：3KB
文件格式：TXT
更新时间：2018-12-03 08:55:50
oracle sql
/*----------------------------------------------------------------------------- * Function : to extract the structure of a table * * Author : Richard Wang * * Date : 2011-08-30 * * Version : 0.1 * ------------------------------------------------------------------------------*/ select 'Table_Name' table_name --,'Column_ID' column_id ,0 column_id ,'Column_Name' column_name ,'Data_Type' data_Type ,'PK_Column' PK_Column ,'Nullable' nullable from DUAL union all select distinct lower(all_c.table_name) table_name --,(case -- when column_id < 10 then '0' || to_char(column_id) -- when column_id < 100 then to_char(column_id) -- when column_id > 100 then 'H' || substrb(to_char(column_id),2,1) -- else '**' -- end ) column_id ,all_c.column_id column_id ,lower(all_c.column_name) column_name ,lower((case all_c.data_type when 'NUMBER' then all_c.data_type || decode(all_c.data_precision,0,'',NULL,'','(' || to_char(all_c.data_precision)) || decode(all_c.data_precision,0,'',NULL,'', decode(all_c.data_scale,0,'',NULL,'', ',' || to_char(all_c.data_scale)) || ')') when 'CHAR' then all_c.data_type || '(' || to_char(all_c.char_length) || ')' when 'VARCHAR2' then all_c.data_type || '(' || to_char(all_c.char_length) || ')' else all_c.data_type end)) data_type ,decode(tab_pk.pk_col_name,NULL,'',tab_pk.pk_col_name,'Y','N/A') PK_column ,all_c.nullable nullable from all_tab_columns all_c left outer join (select cu.table_name pk_tab_name ,cu.column_name pk_col_name from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P') tab_pk on all_c.table_name = tab_pk.pk_tab_name and all_c.column_name = tab_pk.pk_col_name where owner not like '%SYS%' and table_name = upper('V_CHL_PORTFOLIO') -- upper case-- order by table_name,column_id,column_name select * from all_tab_columns where table_name = 'CIS_INF_PARM_VALUE' select distinct table_name from all_tab_columns where owner not like '%SYS%' and table_name like '%DELIV%' -- upper case-- order by table_name