반응형
/****************************************************************************************************************
-- Title : [PGS9.2] Information View of Table & Columns
-- Reference : dbrang.tisotry.com
-- Key word : table information column information
****************************************************************************************************************/

SELECT c.table_schema || '.' || c.table_name "TABLE"
     , c.column_name "COLUMN"
     , case when c.data_type = 'character' then 'CHAR(' || c.character_maximum_length || ')'
            when c.data_type = 'character varying' then 'VARCHAR(' || c.character_maximum_length || ')'
            when c.data_type = 'numeric' then 'NUMERIC(' || c.numeric_precision || case when c.numeric_scale = 0 then ')'
                                                                                        else ',' || c.numeric_scale || ')'
                                                                                   end
            else upper(c.data_type)
       end "COLUMN_TYPE"
     , coalesce(c.character_octet_length, c.numeric_precision/8) "LENGTH_BT"
     , case when c.is_nullable = 'YES' then 1 else 0 end "IS_NULL"
     , case when u.PK_nm is not null then 1 else 0 end "IS_PK"
     , case when u.FK_nm is not null then 1 else 0 end "IS_FK"
     , case when c.column_default like '%nextval%' then 1 else 0 end "IS_IDENT"
     , case when c.column_default not like '%nextval%' and c.column_default is not null
            then c.column_default
            else null
       end "DFLT"
     , NULL "COMP"
     , c.ordinal_position "COL_ORD"
FROM 
(
    select table_catalog, table_schema, table_name, column_name, ordinal_position, column_default, data_type
         , character_maximum_length, character_octet_length, numeric_precision, numeric_scale, is_nullable
    from information_schema.columns 
) c
left join 
(
    select a.table_schema, a.table_name, a.column_name
         , max(case when constraint_type = 'PRIMARY KEY' then constraint_name end) as PK_nm
         , max(case when constraint_type = 'FOREIGN KEY' then constraint_name end) as FK_nm
    from 
    (
        select tc.table_catalog, tc.table_schema, tc.table_name "table_name", tc.constraint_name, kc.column_name, tc.constraint_type
        from information_schema.table_constraints tc
        inner join information_schema.key_column_usage kc
        on kc.table_name = tc.table_name and kc.table_schema = tc.table_schema
        and tc.constraint_name = kc.constraint_name
        where tc.constraint_type in ('PRIMARY KEY', 'FOREIGN KEY')
    ) a
    group by a.table_schema, a.table_name, a.column_name
) u
on c.table_schema = u.table_schema
and c.table_name = u.table_name
and c.column_name = u.column_name
where c.table_schema not in ('pg_catalog', 'information_schema')
order by c.table_schema, c.table_name, c.ordinal_position;



반응형

+ Recent posts