Table Column Details (dtabcol.sql)

What This Script Does

Displays comprehensive column information for tables including data types, constraints, statistics, and default values.

The Script

rem dtableC.sql
rem
rem  linesize = 500
rem
set linesize 700
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

Usage

-- Basic usage
@dtabcol.sql

Parameters

The script prompts for:

&owner - **: Schema name (use % for all schemas)
&table - **: Table name (use % for all tables)

Required Privileges

SELECT ANY DICTIONARY
-- OR --
```## Sample Output

                                    Table Storage Parameters

OWNER TABLE NAME COLUMN DATA DATA DATA NULL NUM DENSITY NUM NUM LAST SAMPLE GLOBAL USER AVG CHAR NAME TYPE LENGTH SCALE DISTINCT NULLS BUCKETS ANALYZED SIZE STATS STATS COL LENGTH

HR EMPLOYEES SALARY NUMBER 8 2 Y 943 .00106157 0 1 30-MAY-25 1,025 YES NO 4 PHONE_NUMBER VARCHAR2 20 0 Y 843 .00118627 10 1 30-MAY-25 1,025 YES NO 12 LAST_NAME VARCHAR2 25 0 N 287 .00348432 0 1 30-MAY-25 1,025 YES NO 6 25 JOB_ID VARCHAR2 10 0 N 19 .05263158 0 1 30-MAY-25 1,025 YES NO 7 10 HIRE_DATE DATE 7 0 N 951 .00105152 0 1 30-MAY-25 1,025 YES NO 7 FIRST_NAME VARCHAR2 20 0 Y 945 .00105820 10 1 30-MAY-25 1,025 YES NO 6 20 EMPLOYEE_ID NUMBER 6 0 N 1,025 .00097561 0 1 30-MAY-25 1,025 YES NO 4 EMAIL VARCHAR2 25 0 N 1,025 .00097561 0 1 30-MAY-25 1,025 YES NO 12 25 DEPARTMENT_ID NUMBER 4 0 Y 11 .09090909 15 1 30-MAY-25 1,025 YES NO 3 COMMISSION_PCT NUMBER 2 2 Y 7 .14285714 935 75 30-MAY-25 1,025 YES NO 3

## Key Output Columns

- **OWNER**: Schema that owns the table
- **TABLE NAME**: Name of the table
- **COLUMN NAME**: Name of the column
- **DATA TYPE**: Column data type (VARCHAR2, NUMBER, DATE, etc.)
- **DATA LENGTH**: Maximum length/precision of the column
- **DATA SCALE**: Scale for numeric columns
- **NULL**: Whether the column allows NULL values (Y/N)
- **NUM DISTINCT**: Number of distinct values (cardinality)
- **DENSITY**: Statistical density value (1/cardinality)
- **NUM NULLS**: Count of NULL values in the column
- **NUM BUCKETS**: Number of histogram buckets
- **LAST ANALYZED**: When column statistics were last gathered
- **SAMPLE SIZE**: Sample size used for statistics
- **GLOBAL STATS**: Whether statistics are global (Y/N)
- **USER STATS**: Whether statistics are user-defined (Y/N)
- **AVG COL LEN**: Average column length in bytes
- **CHAR LENGTH**: Character length for character columns

## Common Use Cases

**Schema Analysis**
```sql
-- Review table structure and column characteristics
@dtabcol.sql
-- Enter: [appropriate values]

Data Modeling

-- Understand data types and constraints for design decisions
@dtabcol.sql

dtable - Table storage parameters and general information
dindcol - Index column definitions
dindex - Index storage parameters