September 25, 2005: On a better describe

I am a bit disappointed with describe. When I use it to describe a table (or view), it only reports column names, their types and if they have not null constraints. But I also want it to print the comments on a table and its columns. And, I'd appreciate if it told me which columns make a primary key and which tables are referenced by their foreign keys. It would be nice if describe told me, if the described thing is a table or a view. Lastly, I want to know the owner of the table (or view).

Because describe lacks all those little -but imho useful- features, I decided to write my own. It consists of two parts: a package and an SQL script. The script is basically a wrapper called from SQL*Plus and wrapps the call of the procedure in the package.

Here's the specification of the package:

create or replace package desc_table 
  authid current_user
as

  table_does_not_exist exception;
  pragma exception_init(table_does_not_exist, -20010);

  type check_t        is table of long;

  type col_t          is record (name varchar2(30), nullable boolean, datatype varchar2(106), checks check_t);
  type cols_t         is table   of col_t;

  type col_comment_t  is record (pos number, comment user_tab_comments.comments%type);
  type col_comments_t is table   of col_comment_t;

  type table_t        is record (own varchar2(30), nam varchar2(30));
  type tables_t       is table   of table_t;

  type char_to_number is table   of number(2) index by varchar2(30);

  type description    is record (tab           table_t,
                                 tab_type      user_tab_comments.table_type%type, -- 'TABLE', 'VIEW' ..?
                                 tab_comment   user_tab_comments.comments%type,
                                 cols          cols_t,
                                 col_comments  col_comments_t,
                                 pks           char_to_number, -- Position of primary keys
                                 parents       tables_t,
                                 children      tables_t);

  -- table_name: 61 chars maximum: 30 chars schema (optional), 1 char dot (optional), 30 chars username 
  function describe(table_name in varchar2) return description;

  function describe(tab        in table_t ) return description;

end desc_table;
/

Here's the package body:

create or replace package body desc_table as 

  function describe(table_name in varchar2) return description is 
    -- used for dbms_utility.name_resolve:
    util_context       number := 2;
    util_schema        varchar2(30);
    util_part1         varchar2(30);
    util_part2         varchar2(30);
    util_dblink        varchar2(128);
    util_part1_type    number;
    util_object_number number;

    tab                table_t;
  begin
    dbms_utility.name_resolve(table_name, util_context, util_schema, util_part1, util_part2, util_dblink, util_part1_type, util_object_number);

    tab.own := util_schema;
    tab.nam := util_part1;

    return describe(tab);

  exception
    when others then 
      case 
        when sqlcode = -6564 then 
        raise table_does_not_exist;
      else
        dbms_output.put_line('exception: ' || sqlerrm || '(' || sqlcode || ')' ); 
    end case;

  end describe;

  function describe(tab in table_t) return description is
    col_r         col_t;
    ret           description;
    v_table_name  varchar2(30);
    v_table_owner varchar2(30);
    col_pos            number;

  begin

    ret.tab          := tab;

    ret.cols         := cols_t        ();
    ret.col_comments := col_comments_t();
    ret.parents      := tables_t      ();
    ret.children     := tables_t      ();

    select comments,table_type into ret.tab_comment, ret.tab_type from all_tab_comments 
     where table_name = tab.nam and owner = tab.own;

    col_pos := 1;

    for r in (
      select
        t.column_name, t.data_type, t.data_length, t.data_precision, t.data_scale, t.nullable, c.comments
      from
        all_tab_cols t join all_col_comments c on 
          t.table_name  = c.table_name  and 
          t.column_name = c.column_name and
          t.owner       = c.owner
      where
        t.table_name = tab.nam and t.owner = tab.own
      order by
        column_id) loop

      col_r.name       := r.column_name;
      col_r.nullable   := case when r.nullable = 'Y' then true else false end;
      col_r.datatype   := r.data_type;
      col_r.checks     := check_t();

      if r.data_length is not null and r.data_precision is null then
        if r.data_type <> 'DATE' then
          col_r.datatype := col_r.datatype || '(' || r.data_length || ')';
        end if;
      end if;

      if r.data_precision is not null then
        col_r.datatype := col_r.datatype || '(' || r.data_precision;

        if r.data_scale is not null and r.data_scale > 0 then
          col_r.datatype := col_r.datatype || ',' || r.data_scale;
        end if;

        col_r.datatype := col_r.datatype || ')';
      end if;

      ret.cols.extend;
      ret.cols(ret.cols.count) := col_r;

      if r.comments is not null then
        ret.col_comments.extend;
        ret.col_comments(ret.col_comments.count).pos     := col_pos; 
        ret.col_comments(ret.col_comments.count).comment := r.comments;
      end if;
      
      col_pos := col_pos+1;
    end loop;

    -- finding constraints
    for r in (
      select
        r_owner, constraint_name, r_constraint_name, constraint_type, search_condition
      from
        all_constraints
      where
        table_name = tab.nam and owner = tab.own) loop

        if r.constraint_type = 'P' then
          for c in (
            select column_name, table_name, position
              from all_cons_columns
             where constraint_name = r.constraint_name) loop

            ret.pks(c.column_name) := c.position;
          end loop;

          select distinct /* distinct in case a table has two foreign keys to table */
            owner, table_name bulk collect into ret.children
          from
            all_constraints
          where
            r_constraint_name = r.constraint_name and
            owner             = tab.own;

        elsif r.constraint_type = 'R' then -- foreign key

          select owner, table_name into v_table_owner, v_table_name 
            from all_constraints 
           where constraint_name = r.r_constraint_name and owner = r.r_owner;

          ret.parents.extend;
          ret.parents(ret.parents.count).own := v_table_owner;
          ret.parents(ret.parents.count).nam := v_table_name;
        end if;

      end loop;

    return ret;

  end describe;

end;
/

Here's the wrapper SQL script:

set verify off

declare
  description desc_table.description;
  cols        desc_table.cols_t;
  cur_col_no  number;
  i    number;

begin

  description := desc_table.describe('&1');
  cols        := description.cols;

  dbms_output.new_line;
  dbms_output.put_line(' Describing ' || description.tab.own || '.' || description.tab.nam);
  dbms_output.put_line(' Type:      ' || description.tab_type);
  dbms_output.put_line(' Comment:   ' || description.tab_comment);

  dbms_output.put_line(' ------------------------------------------------------------');
  dbms_output.put_line(' Name                           Null?    Type              PK');
  dbms_output.put_line(' ------------------------------ -------- ----------------- --');

  cur_col_no := cols.first;
  while cur_col_no is not null loop
    dbms_output.put(' ');

    dbms_output.put(rpad(cols(cur_col_no).name,30));

    dbms_output.put(' ');

    if (cols(cur_col_no).nullable) then
      dbms_output.put('         ');        
    else
      dbms_output.put('NOT NULL ');
    end if;

    dbms_output.put(rpad(cols(cur_col_no).datatype, 17));

    if description.pks.exists(cols(cur_col_no).name) then
      dbms_output.put(lpad(to_char(description.pks(cols(cur_col_no).name)),3)); 
    else
      dbms_output.put('   ');
    end if;
   
    dbms_output.new_line();
    cur_col_no:=cols.next(cur_col_no);
  end loop;

  if description.parents.count > 0 then
    dbms_output.new_line;
    dbms_output.put_line(' Parents: ');

    for parent_no in 1 .. description.parents.count loop
      dbms_output.put_line('  ' || description.parents(parent_no).own || '.' || description.parents(parent_no).nam);
    end loop;

    dbms_output.new_line;
  end if;

  if description.children.count > 0 then
    dbms_output.new_line;
    dbms_output.put_line(' Children: ');

    for child_no in 1 .. description.children.count loop
      dbms_output.put_line('  ' || description.children(child_no).own || '.' || description.children(child_no).nam);
    end loop;

    dbms_output.new_line;
  end if;

  if description.col_comments.count > 0 then
    dbms_output.new_line;
    dbms_output.put_line(' Column comments:');
    dbms_output.put_line(' -----------------');
    dbms_output.new_line;
  
    for cur_col_idx /* not pos ! */ in 1 .. description.col_comments.count loop
      dbms_output.put(' ');
  
      dbms_output.put_line(cols(description.col_comments(cur_col_idx).pos).name || ': ' || description.col_comments(cur_col_idx).comment);
      dbms_output.new_line;
     
      cur_col_no:=cols.next(cur_col_no);
    end loop;
  end if;

  exception
    when desc_table.table_does_not_exist then
      dbms_output.put_line('no such table: &1');

    when others then
      dbms_output.put_line('unknown exception, ' || sqlerrm || '(' || sqlcode || ')');
end;
/

This wrapper script should be saved into a directory pointed at by SQLPATH or into the current directory. For the following experiments, I named the script desc.sql.

I create two tables which will then be used to be described:

create table desc_test_parent (
  pk_1    number,
  pk_2    number,
  col_1   varchar2(30),
  col_2   date,
  col_3   number,
  primary key (pk_1, pk_2)
);

create table desc_test_child (
  fk_1,
  fk_2,
  col_4   blob,
  col_5   raw(50),
  foreign key (fk_1, fk_2) references desc_test_parent(pk_1, pk_2)
);

Some comments are created on both the tables and some columns:

comment on table  desc_test_parent is 'Parent table';
comment on table  desc_test_child is 'Child table';

comment on column desc_test_parent.pk_1  is 'first part of primary key';
comment on column desc_test_parent.pk_2  is 'second part of primary key';
comment on column desc_test_parent.col_2 is 'Just an idle comment on col_2';

comment on column desc_test_child.fk_1  is 'first part of foreign key';
comment on column desc_test_child.fk_2  is 'second part of foreign key';
comment on column desc_test_child.col_5 is 'Some idle comment on col_5';

In order to be able to describe tables in all schemas, the user executing the package needs the select any table privilege explicitely granted:

connect / as sysdba

grant select any table to rene;

Let's use the thing.

@desc desc_test_parent

Here's the output. As can be seen, it truthfully reports the children (being only RENE.DESC_TEST_CHILD) for this table. Also, all comments are displayed. The column PK displays which columns make the primary key.

 Describing RENE.DESC_TEST_PARENT
 Type:      TABLE
 Comment:   Parent table
 ------------------------------------------------------------
 Name                           Null?    Type              PK
 ------------------------------ -------- ----------------- --
 PK_1                           NOT NULL NUMBER(22)         1
 PK_2                           NOT NULL NUMBER(22)         2
 COL_1                                   VARCHAR2(30)
 COL_2                                   DATE
 COL_3                                   NUMBER(22)

 Children:
  RENE.DESC_TEST_CHILD


 Column comments:
 -----------------

 PK_1: first part of primary key

 PK_2: second part of primary key

 COL_2: Just an idle comment on col_2

Now, describing desc_test_child:

@desc desc_test_child

 Describing RENE.DESC_TEST_CHILD
 Type:      TABLE
 Comment:   Child table
 ------------------------------------------------------------
 Name                           Null?    Type              PK
 ------------------------------ -------- ----------------- --
 FK_1                                    NUMBER(22)
 FK_2                                    NUMBER(22)
 COL_4                                   BLOB(4000)
 COL_5                                   RAW(50)

 Parents:
  RENE.DESC_TEST_PARENT


 Column comments:
 -----------------

 FK_1: first part of foreign key

 FK_2: second part of foreign key

 COL_5: Some idle comment on col_5

Describing a table in another schema:

@desc hr.jobs

 Describing HR.JOBS
 Type:      TABLE
 Comment:   jobs table with job titles and salary ranges. Contains 19 rows.
References with employees and job_history table.
 ------------------------------------------------------------
 Name                           Null?    Type              PK
 ------------------------------ -------- ----------------- --
 JOB_ID                         NOT NULL VARCHAR2(10)       1
 JOB_TITLE                      NOT NULL VARCHAR2(35)
 MIN_SALARY                              NUMBER(6)
 MAX_SALARY                              NUMBER(6)

 Children:
  HR.JOB_HISTORY
  HR.EMPLOYEES


 Column comments:
 -----------------

 JOB_ID: Primary key of jobs table.

 JOB_TITLE: A not null column that shows job title, e.g. AD_VP, FI_ACCOUNTANT

 MIN_SALARY: Minimum salary for a job title.

 MAX_SALARY: Maximum salary for a job title

Finally, what happens if a non existing table is described:

@desc table_no_no

no such table: desc_no_no

Update of November 12, 2005

Janus Christensen sent me the following email:

Hello René,

I've had a look at your "better describe" package and SQL*Plus script
(http://www.adp-gmbh.ch/blog/2005/september/25.html) and have started using
them. I have a few suggestions for enhancements for the code in the package.


1. I've altered the package specification declaration to

    create or replace package desc_table authid current_user as

this way I can stick it in a schema and grant execute to public (and a public
synonym) and use from any schema.


2. The following SQL-statement:

select
  t.column_name, t.data_type, t.data_length, t.data_precision, t.data_scale, t.nullable, c.comments
from
  all_tab_cols t join all_col_comments c on t.table_name = c.table_name and t.column_name = c.column_name
where
  t.table_name = tab.nam and t.owner = tab.own
order by
  column_id

I have changed to:

select
  t.column_name, t.data_type, t.data_length, t.data_precision, t.data_scale, t.nullable, c.comments
  from
    all_tab_columns t inner join all_col_comments c
                              on t.table_name = c.table_name and
                                 t.column_name = c.column_name and
                                 t.owner = c.owner
where
  t.table_name = tab.nam and t.owner = tab.own
order by
  column_id) loop

so that it now also checks for the owner of the table/column in
ALL_COL_COLUMNS, otherwise the SQL will include columns with the same name and
table name from different schemas.


3. Similarly to #2 above I have added a check for the owner in the BULK COLLECT that finds a table's children:

select distinct /* distinct in case a table has two foreign keys to table */
  owner, table_name bulk collect into ret.children
from
  all_constraints
where
  r_constraint_name = r.constraint_name
and owner = tab.own

otherwise it found tables in other schemas that weren't related to the table
being described.

I want to thank Janus for sending me his suggestions. As always, I am very happy to receive feedback, especially if it is improving something I have made.

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback - Follow @renenyffenegger