VIEW: MDSYS.USER_MD_COLUMNS

Source

View Text - Preformatted

select 
       table_name,
       column_name,
       data_type,
       data_length,
       data_precision,
       data_scale,
       0,
       0,
       nullable,
       'N',
       column_id,
       default_length,
       num_distinct,
       low_value,
       high_value
  from all_tab_columns
 where owner = user
   and table_name in ( select mdtname
			 from md$tab
                        where owner = user )
   and column_name not in ( select cname
			      from md$col
                             where owner = user )
union all
select 
       table_name,
       column_name,
       'HHCODE',
       data_length,
       data_precision,
       data_scale,
       b.ndim,
       b.mxl,
       nullable,
       decode(b.pk,0,'N',1,'Y'),
       column_id,
       default_length,
       num_distinct,
       low_value,
       high_value
  from all_tab_columns a, md$col b
 where a.owner = user
   and a.owner = b.owner 
   and a.table_name = b.mdtname
   and a.column_name = b.cname
   and table_name in ( select mdtname
			 from md$tab
                        where owner = user )
   and column_name in ( select cname
			  from md$col
                         where owner = user )

View Text - HTML Formatted

SELECT TABLE_NAME
, COLUMN_NAME
, DATA_TYPE
, DATA_LENGTH
, DATA_PRECISION
, DATA_SCALE
, 0
, 0
, NULLABLE
, 'N'
, COLUMN_ID
, DEFAULT_LENGTH
, NUM_DISTINCT
, LOW_VALUE
, HIGH_VALUE
FROM ALL_TAB_COLUMNS
WHERE OWNER = USER
AND TABLE_NAME IN ( SELECT MDTNAME
FROM MD$TAB
WHERE OWNER = USER )
AND COLUMN_NAME NOT IN ( SELECT CNAME
FROM MD$COL
WHERE OWNER = USER ) UNION ALL SELECT TABLE_NAME
, COLUMN_NAME
, 'HHCODE'
, DATA_LENGTH
, DATA_PRECISION
, DATA_SCALE
, B.NDIM
, B.MXL
, NULLABLE
, DECODE(B.PK
, 0
, 'N'
, 1
, 'Y')
, COLUMN_ID
, DEFAULT_LENGTH
, NUM_DISTINCT
, LOW_VALUE
, HIGH_VALUE
FROM ALL_TAB_COLUMNS A
, MD$COL B
WHERE A.OWNER = USER
AND A.OWNER = B.OWNER
AND A.TABLE_NAME = B.MDTNAME
AND A.COLUMN_NAME = B.CNAME
AND TABLE_NAME IN ( SELECT MDTNAME
FROM MD$TAB
WHERE OWNER = USER )
AND COLUMN_NAME IN ( SELECT CNAME
FROM MD$COL
WHERE OWNER = USER )