유용한 쿼리들

erwin query

                  select if(c.ORDINAL_POSITION = 1, t.table_name, '') as name

                        ,'' as design_level

                                        ,'' as view

                                        ,if(c.ORDINAL_POSITION = 1, t.table_name, '')  as business_name

                                        ,'' as description

                                        ,if(c.ORDINAL_POSITION = 1, t.table_comment, '')  as comment                   

                                        ,c.column_name

                        ,if(c.column_comment = '', c.column_name, c.column_comment) as business_name

                        ,'' as description

                        ,c.column_comment

                        ,c.ORDINAL_POSITION-1 as position

                        -- ,if(c.COLUMN_KEY='PRI', concat('XPK_', c.table_name), '') as primary_k

                        ,if(c.COLUMN_KEY='PRI', 'PRIMARY', '') as primary_k

                        ,'' as Alternate1

                                        ,'' as Alternate2

                                        ,'' as Alternate3

                                        ,'' as Alternate4

                                        ,'' as Alternate5

                                        ,'' as Inversion1

                                        ,'' as Inversion2

                                        ,'' as Inversion3

                        ,if(c.COLUMN_KEY='PRI', concat('PRIMARY(', c.ORDINAL_POSITION, ' Asc)'), '') as index1

                        ,'' as i02,'' as i03,'' as i04,'' as i05,'' as i06,'' as i07,'' as i08,'' as i09,'' as i10,'' as i11,'' as i12,'' as i13,'' as i14, '' as i15

                        ,if(c.IS_NULLABLE='YES', 'Yes', 'No') as is_optional

                        ,c.COLUMN_DEFAULT as default_value

                        ,''

                        ,concat('SQL_', c.DATA_TYPE, '_', ifnull(if(c.DATA_TYPE = 'decimal', c.NUMERIC_PRECISION, c.CHARACTER_MAXIMUM_LENGTH),0)) as name

                        ,concat(c.DATA_TYPE, '_', ifnull(if(c.DATA_TYPE = 'decimal', c.NUMERIC_PRECISION, c.CHARACTER_MAXIMUM_LENGTH),0)) as business_name

                        ,c.DATA_TYPE as data_type

                        ,upper(c.COLUMN_TYPE) as native_name

                        ,upper(c.COLUMN_TYPE) as native_business_name

                        ,ifnull(if(c.DATA_TYPE = 'decimal', c.NUMERIC_PRECISION, c.CHARACTER_MAXIMUM_LENGTH),0) as length

                        ,ifnull(c.NUMERIC_SCALE,0) as scale

                  from information_schema.tables  t

                  join information_schema.`COLUMNS` c

                  on t.table_schema = c.table_schema

                  and t.table_name = c.table_name

                  and t.table_type = 'BASE TABLE'

                  where t.table_schema = 'erpapp' 

                  and t.table_comment <> ''

                  and t.table_name not like 'hr_conv%'

                  and t.table_comment <> 'XFI_임시테이블'

                  order by t.table_schema, t.table_name, c.ORDINAL_POSITION