////오라클의 딕셔너리뷰의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
[출처] 테이블 명세서 출력|작성자 녀석
