Dtablec (dtableC.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script provides comprehensive column-level details for tables, including data types, statistics, and default values. Essential for understanding table structure and column characteristics for development and optimization.

The Script

rem dtableC.sql
rem
rem  linesize = 500
rem
set linesize 500
rem
ttitle 'Table Storage Parameters'
rem
col owner format a8 heading 'OWNER'
col table_lock format a1 heading 'L'
col cache format a1 heading 'C'
col table_name format a34 heading 'TABLE NAME'
col tablespace_name format a10 heading 'TABLESPACE'
col logging format a3 heading 'LOG'
col iot_type format a3 heading 'IOT'
col DATA_TYPE format A9 heading 'DATA|TYPE'
col COLUMN_NAME format A26 heading 'COLUMN|NAME'
col DATA_LENGTH format 9,999 heading 'DATA|LENGTH'
col DATA_SCALE format 9,999 heading 'DATA|SCALE'
col NUM_DISTINCT format 999,999,999 heading 'NUM|DISTINCT'
col NULL format A4 heading 'NULL'
rem
break on owner skip 1
rem
select owner,
       table_name,COLUMN_NAME            ,
DATA_TYPE,
DATA_LENGTH,
DATA_SCALE  ,
NULLABLE     ,
NUM_DISTINCT   ,
DENSITY           ,
NUM_NULLS          ,
NUM_BUCKETS         ,
LAST_ANALYZED        ,
SAMPLE_SIZE           ,
GLOBAL_STATS           ,
USER_STATS             ,
AVG_COL_LEN            ,
CHAR_LENGTH  ,
--HISTOGRAM              ,
DATA_DEFAULT
  from sys.dba_tab_columns
 where owner like nvl(upper('&owner'),'%')
   and table_name like nvl(upper('&table'),'%')
ORDER BY COLUMN_NAME DESC
-- order by owner, table_name;
rem
set linesize 80

Parameters

The script prompts for:

• &Schema - owner to filter (use % for all)
• &Table - name pattern to filter (use % for all)

Usage

-- Basic usage
@dtablec.sql

-- When prompted, enter:
-- owner: Schema owner to filter (use % for all)
-- table: Table name pattern to filter (use % for all)

Sample Output

Table Storage Parameters

OWNER    TABLE NAME                         COLUMN                      DATA       DATA   DATA                 NUM
                                           NAME                        TYPE     LENGTH  SCALE NULLABLE     DISTINCT     DENSITY   NUM_NULLS NUM_BUCKETS LAST_ANALYZED SAMPLE_SIZE GLOBAL_STATS USER_STATS AVG_COL_LEN CHAR_LENGTH DATA_DEFAULT
-------- ---------------------------------- -------------------------- --------- ------ ------ -------- ------------ ----------- ----------- ----------- ------------- ----------- ------------ ---------- ----------- ----------- ----------------
HR       EMPLOYEES                          SALARY                     NUMBER        22      2 Y                  58   0.0172414           0           1 05-JAN-25           107 YES          NO                    5
                                           PHONE_NUMBER               VARCHAR2      20        Y                 107   0.0093458           0           1 05-JAN-25           107 YES          NO                   13
                                           MANAGER_ID                 NUMBER        22        Y                  18   0.0555556           1           1 05-JAN-25           107 YES          NO                    4
                                           LAST_NAME                  VARCHAR2      25        N                 102   0.0098039           0           1 05-JAN-25           107 YES          NO                    8
                                           JOB_ID                     VARCHAR2      10        N                  19   0.0526316           0           1 05-JAN-25           107 YES          NO                    8
                                           HIRE_DATE                  DATE           7        N                  98   0.0102041           0           1 05-JAN-25           107 YES          NO                    8
                                           FIRST_NAME                 VARCHAR2      20        Y                  91   0.0109890           0           1 05-JAN-25           107 YES          NO                    7
                                           EMPLOYEE_ID                NUMBER        22        N                 107   0.0093458           0           1 05-JAN-25           107 YES          NO                    4
                                           EMAIL                      VARCHAR2      25        N                 107   0.0093458           0           1 05-JAN-25           107 YES          NO                    9
                                           DEPARTMENT_ID              NUMBER        22        Y                  11   0.0000893           1          12 05-JAN-25           107 YES          NO                    3
                                           COMMISSION_PCT             NUMBER        22      2 Y                   7   0.1428571          72           1 05-JAN-25           107 YES          NO                    3