文件名称:
oracle sql of extracting table structure
开发工具:
文件大小: 3kb
下载次数: 0
上传时间: 2015-11-13
详细说明： /*----------------------------------------------------------------------------- * 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 ...展开收缩
(系统自动生成,下载前可以参看下载内容)
下载文件列表
相关说明
- 本站资源为会员上传分享交流与学习,如有侵犯您的权益,请联系我们删除.
- 本站是交换下载平台，提供交流渠道，下载内容来自于网络，除下载问题外，其它问题请自行百度。
- 本站已设置防盗链，请勿用迅雷、QQ旋风等多线程下载软件下载资源，下载后用WinRAR最新版进行解压.
- 如果您发现内容无法下载，请稍后再次尝试；或者到消费记录里找到下载记录反馈给我们.
- 下载后发现下载的内容跟说明不相乎，请到消费记录里找到下载记录反馈给我们，经确认后退回积分.
- 如下载前有疑问，可以通过点击"提供者"的名字，查看对方的联系方式，联系对方咨询.
