table_info.sql

来自「oracle dal gen,生成.NET ORACLE DAL层代码」· SQL 代码 · 共 25 行

  CREATE OR REPLACE FORCE VIEW TABLE_INFO as
  select   
    utc.table_name,
    utc.column_id,
    utc.column_name,
    utc.data_type,
    ucc.constraint_name,
    ucc.position,
    uc.constraint_type,
    uc.r_constraint_name,
    ucc_r.table_name as r_table_name,
    ucc_r.column_name as r_column_name,
    ucc_r.position as r_position,
    case when (uc.constraint_type = 'R') then 1 else 0 end as fk,
    case when (uc.constraint_type = 'P') then 1 else 0 end as pk          
  from
    user_tab_columns utc
  left join user_cons_columns ucc on ucc.table_name = utc.table_name and ucc.column_name = utc.column_name
  left join user_constraints uc on uc.table_name = ucc.table_name and uc.constraint_name = ucc.constraint_name    
  left join user_cons_columns ucc_r on ucc_r.constraint_name = uc.r_constraint_name
  order by 
    column_id
;