sql | Oracle‎ > ‎ora | PL/SQL‎ > ‎

ora | Select Column Data Type

Tables to use:
    ALL_COL_COMMENTS
    =======================================================
        OWNER
        TABLE_NAME
        COLUMN_NAME
        COMMENTS
    
    ALL_TAB_COLUMNS
    =======================================================
        OWNER
        TABLE_NAME
        COLUMN_NAME
        DATA_TYPE
        DATA_TYPE_MOD
        DATA_TYPE_OWNER
        DATA_LENGTH
        DATA_PRECISION
        DATA_SCALE
        NULLABLE
        COLUMN_ID
        DEFAULT_LENGTH
        DATA_DEFAULT
        NUM_DISTINCT
        LOW_VALUE
        HIGH_VALUE
        DENSITY
        NUM_NULLS
        NUM_BUCKETS
        LAST_ANALYZED
        SAMPLE_SIZE
        CHARACTER_SET_NAME
        CHAR_COL_DECL_LENGTH
        GLOBAL_STATS
        USER_STATS
        AVG_COL_LEN
        CHAR_LENGTH
        CHAR_USED
        V80_FMT_IMAGE
        DATA_UPGRADED
        HISTOGRAM
 
    ALL_TABLES
    =======================================================
        OWNER
        TABLE_NAME
        TABLESPACE_NAME
        CLUSTER_NAME
        IOT_NAME
        STATUS
        PCT_FREE
        PCT_USED
        INI_TRANS
        MAX_TRANS
        INITIAL_EXTENT
        NEXT_EXTENT
        MIN_EXTENTS
        MAX_EXTENTS
        PCT_INCREASE
        FREELISTS
        FREELIST_GROUPS
        LOGGING
        BACKED_UP
        NUM_ROWS
        BLOCKS
        EMPTY_BLOCKS
        AVG_SPACE
        CHAIN_CNT
        AVG_ROW_LEN
        AVG_SPACE_FREELIST_BLOCKS
        NUM_FREELIST_BLOCKS
        DEGREE
        INSTANCES
        CACHE
        TABLE_LOCK
        SAMPLE_SIZE
        LAST_ANALYZED
        PARTITIONED
        IOT_TYPE
        TEMPORARY
        SECONDARY
        NESTED
        BUFFER_POOL
        ROW_MOVEMENT
        GLOBAL_STATS
        USER_STATS
        DURATION
        SKIP_CORRUPT
        MONITORING
        CLUSTER_OWNER
        DEPENDENCIES
        COMPRESSION
        DROPPED
       
SQL statement:
 
SELECT  
     S.OWNER
    ,S.TABLE_NAME
    ,S.COLUMN_ID
    ,S.COLUMN_NAME
    ,S.DATA_TYPE
    ,S.DATA_LENGTH
    ,S.DATA_PRECISION
    ,S.DATA_DEFAULT
    ,S.NULLABLE
    ,R.COMMENTS
FROM  
    ALL_TAB_COLUMNS S
    JOIN ALL_TABLES T 
        ON S.OWNER = T.OWNER 
        AND S.TABLE_NAME = T.TABLE_NAME
    LEFT JOIN ALL_COL_COMMENTS R 
        ON S.OWNER = R.OWNER 
        AND S.TABLE_NAME = R.TABLE_NAME 
        AND S.COLUMN_NAME = R.COLUMN_NAME
WHERE 
    S.OWNER = 'ARCU' 
    AND S.TABLE_NAME = 'BM_LINE'
ORDER BY 
     S.TABLE_NAME
    ,S.COLUMN_ID
Comments