VIEW: MDSYS.ALL_MD_COLUMNS

Source

View Text - Preformatted

select owner,
       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
                           or mdtname in ( select table_name
                                             from all_tab_privs
                                            where grantee = user ) )
   and column_name not in ( select cname
                              from md$col
                             where owner = user
                                or mdtname in ( select table_name
                                                  from all_tab_privs
                                                 where grantee = user ) )
union all
select a.owner,
       a.table_name,
       a.column_name,
       'HHCODE',
       a.data_length,
       a.data_precision,
       a.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 a.table_name in ( select mdtname
                           from md$tab
                          where owner = user
                             or mdtname in ( select table_name
                                               from all_tab_privs
                                              where grantee = user ) )
   and a.column_name in ( select cname
                            from md$col
                           where owner = user
                              or mdtname in ( select table_name
                                                from all_tab_privs
                                               where grantee = user ) )

View Text - HTML Formatted

SELECT OWNER
, 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 OR MDTNAME IN ( SELECT TABLE_NAME
FROM ALL_TAB_PRIVS
WHERE GRANTEE = USER ) )
AND COLUMN_NAME NOT IN ( SELECT CNAME
FROM MD$COL
WHERE OWNER = USER OR MDTNAME IN ( SELECT TABLE_NAME
FROM ALL_TAB_PRIVS
WHERE GRANTEE = USER ) ) UNION ALL SELECT A.OWNER
, A.TABLE_NAME
, A.COLUMN_NAME
, 'HHCODE'
, A.DATA_LENGTH
, A.DATA_PRECISION
, A.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 A.TABLE_NAME IN ( SELECT MDTNAME
FROM MD$TAB
WHERE OWNER = USER OR MDTNAME IN ( SELECT TABLE_NAME
FROM ALL_TAB_PRIVS
WHERE GRANTEE = USER ) )
AND A.COLUMN_NAME IN ( SELECT CNAME
FROM MD$COL
WHERE OWNER = USER OR MDTNAME IN ( SELECT TABLE_NAME
FROM ALL_TAB_PRIVS
WHERE GRANTEE = USER ) )