Elephant11

////오라클의 딕셔너리뷰의table_name,column_name,data_type등등은 대문자로 저장되어있다.
//테이블 정의를 출력
select a.table_name table_name
 ,b.comments tab_comments
 ,c.column_id column_id
 ,c.column_name column_name
 ,d.comments col_comments
 ,c.data_type data_type
 ,decode(c.data_type,'CHAR'    ,to_char(c.data_length)
        ,'VARCHAR' ,to_char(c.data_length)
        ,'VARCHAR2',to_char(c.data_length)
        ,'NUMBER'  ,'('||to_char(c.data_precision)||','||to_char(c.data_scale)||')'
            )data_length
 ,decode(e.column_name,c.column_name,'Pk') pk_flag
 ,c.nullable nullable
 ,c.data_default data_default
 from user_tables a
 ,user_tab_comments b
 ,user_tab_columns c
 ,user_col_comments d
 ,( select f.table_name table_name
    ,f.column_name column_name
    from user_constraints e
    ,user_cons_columns f
   where e.constraint_name = f.constraint_name(+)
   and e.table_name = f.table_name(+)
   and e.constraint_type(+) = 'P'
 )e
where
//   a.table_name between 'GFC' and 'GFCZ'
  a.table_name like 'GFC%'
and a.table_name = b.table_name
and a.table_name = c.table_name
and a.table_name = d.table_name
and c.column_name= d.column_name
and c.table_name = e.table_name(+)
and c.column_name= e.column_name(+)
order by a.table_name,c.column_id
;

//테이블 프라이머리키 출력(1)
select f.table_name table_name
 ,f.constraint_name constraint_name
 ,f.position  position
 ,f.column_name column_name
 from user_constraints e
 ,user_cons_columns f
where f.table_name like 'GFC%'
and e.constraint_name = f.constraint_name(+)
and e.table_name = f.table_name(+)
and e.constraint_type(+) = 'P'
order by f.table_name,f.position
;

//테이블 프라이머리키 출력(2) <- 프라이머리키를 구성하는 칼람들을 한칼람으로 구성(키를 구성하는 칼람갯수는 최고 10개로 생각함)
select f.table_name table_name
 ,min(f.constraint_name) primary_name
 ,replace(rtrim(min(decode(f.position,1,f.column_name))||' '
 ||min(decode(f.position,2,f.column_name))||' '
 ||min(decode(f.position,3,f.column_name))||' '
 ||min(decode(f.position,4,f.column_name))||' '
 ||min(decode(f.position,5,f.column_name))||' '
 ||min(decode(f.position,6,f.column_name))||' '
 ||min(decode(f.position,7,f.column_name))||' '
 ||min(decode(f.position,8,f.column_name))||' '
 ||min(decode(f.position,9,f.column_name))||' '
 ||min(decode(f.position,10,f.column_name))),' ',',') column_name
 from user_constraints e
 ,user_cons_columns f
where f.table_name like 'GFC%'
and e.constraint_name = f.constraint_name(+)
and e.table_name = f.table_name(+)
and e.constraint_type(+) = 'P'
group by f.table_name
;

//테이블 인덱스키 출력(1)
select h.table_name
 ,h.index_name
 ,h.column_position
 ,h.column_name
 ,g.uniqueness
 from user_indexes g
 ,user_ind_columns h
where g.table_name like 'GFC%'
and g.index_name = h.index_name
order by h.table_name,g.uniqueness desc,h.index_name,h.column_position
;

//테이블 인덱스키 출력(2) <- 인덱스키를 구성하는 칼람들을 한칼람으로 구성(키를 구성하는 칼람갯수는 최고 10개로 생각함)
select h.table_name
 ,h.index_name
 ,replace(rtrim(min(decode(h.column_position,1,h.column_name))||' '
 ||min(decode(h.column_position,2,h.column_name))||' '
 ||min(decode(h.column_position,3,h.column_name))||' '
 ||min(decode(h.column_position,4,h.column_name))||' '
 ||min(decode(h.column_position,5,h.column_name))||' '
 ||min(decode(h.column_position,6,h.column_name))||' '
 ||min(decode(h.column_position,7,h.column_name))||' '
 ||min(decode(h.column_position,8,h.column_name))||' '
 ||min(decode(h.column_position,9,h.column_name))||' '
 ||min(decode(h.column_position,10,h.column_name))),' ',',') column_name 
 ,min(g.uniqueness) uniqueness
 from user_indexes g
 ,user_ind_columns h
where g.table_name like 'GFC%'
and g.index_name = h.index_name
group by h.table_name,h.index_name
order by h.table_name,uniqueness desc,h.index_name
;

/********************************************************************************************************
*****
 테이블 및 인덱스 정보
*********************************************************************************************************
****/

SELECT
 CASE WHEN ColumnName = '-' THEN TableName
   WHEN ColumnName = 'INDEX INFO' THEN ColumnName
   ELSE '-'
 END TableName
, CASE WHEN ColumnName = 'INDEX INFO' THEN '-'
   ELSE ColumnName
 END ColumnName
, ColumnComment
, CASE WHEN ColumnDataType = '-' THEN ''
   WHEN ColumnDataType = 'COLUMN DATATYPE' THEN ColumnDataType
   WHEN ColumnName = 'INDEX INFO' THEN ColumnDataType
   ELSE ColumnDataType + ' (' + CAST(ColumnDataLen AS VARCHAR(10)) + ')'
 END ColumnDataType
, IsIdentity, IsNullable
, Collation_name, Definition DefaultValue
FROM (
 SELECT
  ST.object_Id TableID
 , SC.column_id ColumnID
 , ST.name   TableName
 , SC.name   ColumnName
 , CCM.VALUE  ColumnComment
 , STY.name  ColumnDataType
 , SC.Max_length ColumnDataLen
 , CASE WHEN SC.is_identity = 1 THEN 'Y' ELSE 'N' END IsIdentity 
 , CASE WHEN SC.is_nullable = 1 THEN 'Y' ELSE 'N' END IsNullable 
 , SC.Collation_name
 , SD.Definition
 FROM sys.tables ST
  INNER JOIN sys.columns SC
   ON ST.object_id = SC.object_id
  INNER JOIN sys.types STY
   ON STY.system_type_id = SC.system_type_id
    AND STY.user_type_id = SC.user_type_id
  LEFT OUTER JOIN ( SELECT major_id, minor_id, VALUE
       FROM sys.extended_properties
       WHERE class = 1) CCM
   ON SC.object_id = CCM.major_id AND SC.column_id = CCM.minor_id
  LEFT OUTER JOIN (SELECT parent_object_id, parent_column_id, Definition FROM
sys.default_constraints) SD
   ON SC.object_id = SD.parent_object_id AND SC.column_id =
SD.parent_column_id
 UNION ALL
 SELECT
  object_id , -2 , name
 , '-' , '-' , '-' , 0 , '-' , '-' , '-' , '-'
 FROM sys.tables
 UNION ALL
 SELECT  object_id , 0 , '--'
 , 'COLUMN NAME' , 'DESCRIPTION' , 'COLUMN DATATYPE' ,
0 , 'ISIDENTITY' , 'ISNULLABLE' , 'COLLATION_NAME' ,'DEFAULT VALUE'
 FROM sys.tables
 UNION ALL 
 SELECT
  object_id, -1, OBJECT_NAME(object_id), 'INDEX INFO', NAME, type_desc , 0, idx_info, '-
' , '-' , '-'
 FROM ( SELECT D.object_id, IX.NAME, IX.type_desc COLLATE Korean_Wansung_CI_AS_KS AS
type_desc,
    CASE WHEN IX.is_unique = 1 THEN 'UNIQUE' + ' , ' ELSE '' END
   + CASE WHEN IX.is_primary_key = 1 THEN 'PK' + ' , ' ELSE '' END
   + ISNULL(MAX(CASE WHEN column_id = 1 THEN D.name + '' +
CASE WHEN IX.is_disabled = 0 THEN '( ASC )' ELSE '( DESC )' END + ' ' END ) , '')
   + ISNULL(MAX(CASE WHEN column_id = 2 THEN D.name + '' +
CASE WHEN IX.is_disabled = 0 THEN '( ASC )' ELSE '( DESC )' END + ' ' END ) , '')
   + ISNULL(MAX(CASE WHEN column_id = 3 THEN D.name + '' +
CASE WHEN IX.is_disabled = 0 THEN '( ASC )' ELSE '( DESC )' END + ' ' END ) , '')
   + ISNULL(MAX(CASE WHEN column_id = 4 THEN D.name + '' +
CASE WHEN IX.is_disabled = 0 THEN '( ASC )' ELSE '( DESC )' END + ' ' END ) , '')
   + ISNULL(MAX(CASE WHEN column_id = 5 THEN D.name + '' +
CASE WHEN IX.is_disabled = 0 THEN '( ASC )' ELSE '( DESC )' END + ' ' END ) , '')
   + ISNULL(MAX(CASE WHEN column_id = 6 THEN D.name + '' +
CASE WHEN IX.is_disabled = 0 THEN '( ASC )' ELSE '( DESC )' END + ' ' END ) , '')
   + ISNULL(MAX(CASE WHEN column_id = 8 THEN D.name + '' +
CASE WHEN IX.is_disabled = 0 THEN '( ASC )' ELSE '( DESC )' END + ' ' END ) , '')
   + ISNULL(MAX(CASE WHEN column_id = 9 THEN D.name + '' +
CASE WHEN IX.is_disabled = 0 THEN '( ASC )' ELSE '( DESC )' END + ' ' END ) , '')
   + ISNULL(MAX(CASE WHEN column_id = 10 THEN D.name + '' +
CASE WHEN IX.is_disabled = 0 THEN '( ASC )' ELSE '( DESC )' END + ' ' END ) , '') idx_info
   FROM (SELECT a.object_id, a.name, b.column_id, b.index_id FROM
sys.columns a INNER JOIN sys.index_columns b ON a.object_id = b.object_id AND a.column_id = b.column_id
AND OBJECTPROPERTY(a.object_id, 'IsUserTable') = 1 ) D
   INNER JOIN sys.indexes IX
    ON IX.object_id = D.object_id AND IX.index_id = D.index_id
   GROUP BY D.object_id, D.index_id, IX.NAME, IX.type_desc, IX.is_unique,
IX.is_primary_key, IX.is_disabled
 ) TIDX
) TBL
ORDER BY TableID, ColumnID

FOREIGN KEY