jdesc.sql – A better describe for Oracle

When I first started using PostgreSQL one thing I really liked was their describe command. Unlike Oracle’s which just gives you the table structure (columns), PostgreSQL also shows you the constraints, indexes, triggers,etc. Per the Oracle manual describe “Lists the column definitions for the specified table, view or synonym, or the specifications for the specified function or procedure.” So, it is what it is, but it should be more.

As a result I wrote my own “describe” SQL script so I could enjoy the same thing in Oracle. I’ve been using it for numerous years now and it is great to have. I always have this in my SQLPATH and one day I might just make it a procedure.

Thank you to Deanna McMurray for asking for an update to allow calling this from a schema other than the one you are logged in to. This helped push me to finally add that feature and quite a few others than I wanted included.

Here is your standard Oracle describe result:

SQL> desc some_data
Name                                                              Null?    Type
----------------------------------------------------------------- -------- --------------------------------------------
SOME_DATA_ID                                                       NOT NULL NUMBER
SOME_DATA_TYPE_ID                                                  NOT NULL NUMBER
MAC_ADDR                                                           VARCHAR2(30)
VALUE                                                              VARCHAR2(64)
LAST_MODIFIED_DATE                                                 DATE

With jdesc.sql you can get much more information:

SQL> @jdesc some_date
 <or>
SQL> @jdesc thatuser.some_data

THATUSER.SOME_DATA

Temporary Table => NO
Index Organized Table (IOT) => NO
Partitioned => NO
Clustered Table => NO
Compressed Table => NO
Materialized Views => NO
Fine Grained Access Control => NO

There are no synonyms for this table.

COLUMN                                  TYPE            NULLS           DEFAULT
------                                  ----            -----           -------
SOME_DATA_ID                            NUMBER(,)       NOT NULL  
SOME_DATA_TYPE_ID                       NUMBER(,)       NOT NULL  
MAC_ADDR                                VARCHAR2(30)    NULL      
VALUE                                   VARCHAR2(64)    NULL      
LAST_MODIFIED_DATE                      DATE            NULL      

CONSTRAINT                              TYPE            CRITERIA
----------                              ----            --------
PK_SOME_DATA                            Primary         (SOME_DATA_ID)
FK_SOME_DATA_01                         References      SOME(MAC_ADDR) CASCADE
FK_SOME_DATA_02                         References      SOME_DATA_TYPE(SOME_DATA_TYPE_ID)
SYS_C0023736                            Check           "SOME_DATA_ID" IS NOT NULL
SYS_C0023737                            Check           "SOME_DATA_TYPE_ID" IS NOT NULL

INDEX                                   TYPE                    COLUMNS
-----                                   ----                    -------
PK_SOME_DATA                            UNIQUE:NORMAL           (SOME_DATA_ID)
IDX_SOME_DATA_02                        NONUNIQUE:NORMAL        (MAC_ADDR)
IDX_SOME_DATA_03                        NONUNIQUE:NORMAL        (SOME_DATA_TYPE_ID)

TRIGGER                                 TYPE                    EVENT
-----                                   ----                    ------
<none>

For lack of a better name, and since I wrote it, I called it jdesc.sql. Here is the SQL:

/*

Created: Version 1: 01/30/2012 Jed S. Walker
Updated: Version 2: 03/20/2006 Jed S. Walker

Notes :
       does not work on Oracle <9i due to Mview check
To do:
       does not handle "" table names 
Usage:
       @jdesc user.table 
       @jdesc table
*/

set serveroutput on size 1000000

set verify off
set feedback off

set pages 50 linesize 120
set long 10000

col owner format a20
col object_name format a35

declare
  n_exist        number;
  v_param1       varchar2(200);
  v_seploc       number;
  v_schema_name  user_tables.table_name%type;
  v_table_name   user_tables.table_name%type;
  v_longmsg      varchar2(4000);
  v_type_atts    varchar2(500);
  cursor curs_syns (owner_val varchar2, tabname_val varchar2) 
  is select owner, synonym_name
     from all_synonyms
     where owner = owner_val
     and table_name = tabname_val
     order by owner, synonym_name;
  cursor curs_tcols (owner_val varchar2, tabname_val varchar2) 
  is select column_name, data_type, 
            data_length, data_precision, data_scale,
            decode(nullable,'Y','NULL','NOT NULL') nullable, 
            data_default
     from all_tab_columns
     where owner = owner_val
     and table_name = tabname_val
     order by column_id;
  v_lenprec  varchar2(20);
  cursor curs_cons (owner_val varchar2, tabname_val varchar2)
  is select constraint_name, 
            decode(constraint_type, 'P', 'Primary', 'U', 'Unique',
                                    'C', 'Check', 'R', 'References', constraint_type)
                  constraint_type, 
            search_condition, r_constraint_name, 
            decode(delete_rule, 'NO ACTION', '', delete_rule) delete_rule,
            decode(constraint_type, 'P', 1, 'U', 2, 'R', 3, 'C', 4, 5) sortorder
     from all_constraints
     where owner = owner_val
     and table_name = tabname_val
     order by sortorder, constraint_name;
  v_fk_table_name   user_tables.table_name%type;
  v_temp            varchar2(2000);
  cursor curs_ccollist (owner_val varchar2, consname_val varchar2)
  is select column_name
     from all_cons_columns
     where owner = owner_val
     and constraint_name = consname_val
     order by position;
  v_criteria   varchar2(2000);
  cursor curs_inds (owner_val varchar2, tabname_val varchar2)
  is select index_name, index_type, uniqueness 
     from all_indexes
     where owner = owner_val
     and table_name = tabname_val
     order by uniqueness desc, index_name;
  cursor curs_icollist (owner_val varchar2, indname_val varchar2)
  is select column_name
     from all_ind_columns
     where index_owner = owner_val
     and index_name = indname_val
     order by column_position;
  v_columns   varchar2(2000);
  cursor curs_trigs (owner_val varchar2, tabname_val varchar2) 
  is select trigger_name, trigger_type, triggering_event
     from all_triggers
     where owner = owner_val
     and table_name = tabname_val
     order by trigger_type, triggering_event, trigger_name;
begin

  -- get parameters and determine if this is local schema or other schema 
  v_param1:='&1';
  v_seploc:=instr(v_param1,'.');
  if v_seploc = 0 then
    v_schema_name:=upper(SYS_CONTEXT('USERENV','CURRENT_SCHEMA'));
    v_table_name:=upper(v_param1);
  else
    v_schema_name:=upper(substr(v_param1,1,v_seploc-1));
    v_table_name:=upper(substr(v_param1,v_seploc+1,length(v_param1)-v_seploc));
  end if;

  -- check for table existence or view
  select count(1) into n_exist
  from all_tables
  where owner = v_schema_name
  and table_name = v_table_name;
  if n_exist = 0 then
    select count(1) into n_exist
    from all_views
    where owner = v_schema_name
    and view_name = v_table_name;
    if n_exist = 0 then
      dbms_output.put_line('Table ' || v_schema_name || '.' || v_table_name || ' does not exist');
    else
      dbms_output.put_line(chr(10) || v_schema_name || '.' || v_table_name || ' is a view:' || chr(10));
      select text into v_longmsg from all_views where owner = v_schema_name and view_name = v_table_name; 
      dbms_output.put_line(v_longmsg);
    end if;
  else

    -- show table name and type attributes
    dbms_output.put_line(chr(10) || v_schema_name || '.' || v_table_name || chr(10));
    -- show table type attributes
    select decode(temporary,'Y','YES','N','NO') into v_temp
    from all_tables
    where owner = v_schema_name
    and table_name = v_table_name;
    dbms_output.put_line('Temporary Table => ' || v_temp);
    select decode(iot_type,'IOT','YES','NO') into v_temp
    from all_tables
    where owner = v_schema_name
    and table_name = v_table_name;
    dbms_output.put_line('Index Organized Table (IOT) => ' || v_temp);
    select partitioned into v_temp 
    from all_tables
    where owner = v_schema_name
    and table_name = v_table_name;
    dbms_output.put_line('Partitioned => ' || v_temp);
    select decode(cluster_name,null,'NO','YES') into v_temp
    from all_tables
    where owner = v_schema_name
    and table_name = v_table_name;
    dbms_output.put_line('Clustered Table => ' || v_temp);
    select decode(compression,'ENABLED','YES','NO') into v_temp
    from all_tables
    where owner = v_schema_name
    and table_name = v_table_name;
    dbms_output.put_line('Compressed Table => ' || v_temp);
    select decode(count(1),0,'NO','YES') into v_temp
    from all_base_table_mviews
    where owner = v_schema_name
    and master = v_table_name;
    dbms_output.put_line('Materialized Views => ' || v_temp);
    select decode(count(1),0,'NO','YES') into v_temp
    from all_policies
    where object_owner = v_schema_name
    and object_name = v_table_name;
    dbms_output.put_line('Fine Grained Access Control => ' || v_temp);

    -- show synonyms
    dbms_output.put_line(chr(10));
    n_exist:=0;
    for rec_syn in curs_syns (v_schema_name,v_table_name) loop
      dbms_output.put_line('Synonym: ' || rec_syn.owner || ' : ' || rec_syn.synonym_name);
      n_exist:=n_exist+1;
    end loop;
    if n_exist = 0 then
      dbms_output.put_line('There are no synonyms for this table.');
    end if;

    -- list columns, data type, null?, default
    dbms_output.put_line(chr(10) || rpad('COLUMN',32) || chr(9) ||
                                    rpad('TYPE',14) || chr(9) ||
                                    rpad('NULLS',10) || chr(9) ||
                                    'DEFAULT');
    dbms_output.put_line(rpad('------',32) || chr(9) ||
                         rpad('----',14) || chr(9) ||
                         rpad('-----',10) || chr(9) ||
                         '-------');
    for rec_col in curs_tcols (v_schema_name,v_table_name) loop
      if rec_col.data_type in ('CHAR','VARCHAR2','NCHAR','NVARCHAR2') then
        v_lenprec:='(' || rec_col.data_length || ')';
      elsif rec_col.data_type in ('NUMBER') then
        v_lenprec:='(' || rec_col.data_precision || ',' || rec_col.data_scale || ')';
      else
        v_lenprec:='';
      end if;
      dbms_output.put_line(rpad(rec_col.column_name,32) || chr(9) || 
                           rpad(rec_col.data_type || v_lenprec,14) || chr(9) ||
                           rpad(rec_col.nullable,10) || chr(9) ||
                           rec_col.data_default);
    end loop;

    -- constraints
    dbms_output.put_line(chr(10) || rpad('CONSTRAINT',32) || chr(9) ||
                                    rpad('TYPE',14) || chr(9) ||
                                    'CRITERIA');
    dbms_output.put_line(rpad('----------',32) || chr(9) ||
                         rpad('----',14) || chr(9) ||
                         '--------');
    n_exist:=0;
    for rec_con in curs_cons (v_schema_name,v_table_name) loop
      if rec_con.constraint_type in ('Primary','Unique') then
        v_criteria:='(';
        for rec_collist in curs_ccollist(v_schema_name,rec_con.constraint_name) loop
          if v_criteria != '(' then 
            v_criteria:=v_criteria||','; 
          end if;
          v_criteria:=v_criteria || rec_collist.column_name; 
        end loop;
        v_criteria:=v_criteria || ')';
      elsif rec_con.constraint_type = 'References' then
        --v_criteria:=rec_con.r_constraint_name;
        select table_name into v_fk_table_name
        from all_constraints
        where owner = v_schema_name
        and constraint_name = rec_con.r_constraint_name;
        v_temp:='(';
        for rec_collist in curs_ccollist(v_schema_name,rec_con.r_constraint_name) loop
          if v_temp != '(' then 
            v_temp:=v_temp||','; 
          end if;
          v_temp:=v_temp || rec_collist.column_name; 
        end loop;
        v_temp:=v_temp || ')';
        v_criteria:=v_fk_table_name || v_temp || ' ' || rec_con.delete_rule;
      elsif rec_con.constraint_type = 'Check' then
        v_criteria:=rec_con.search_condition;
        if length(v_criteria) > (250-32-14) then
          v_criteria:=substr(v_criteria,1,(250-32-14)) || '...';
        end if;
      else
        v_criteria:='Unknown';
      end if;
      n_exist:=n_exist+1;
      dbms_output.put_line(rpad(rec_con.constraint_name,32) || chr(9) || 
                           rpad(rec_con.constraint_type,14) || chr(9) ||
                           v_criteria);
    end loop;
    if n_exist = 0 then
      dbms_output.put_line('<none>');
    end if;

    -- indexes
    dbms_output.put_line(chr(10) || rpad('INDEX',32) || chr(9) ||
                                    rpad('TYPE',20) || chr(9) ||
                                    'COLUMNS');
    dbms_output.put_line(rpad('-----',32) || chr(9) ||
                         rpad('----',20) || chr(9) ||
                         '-------');
    n_exist:=0;
    for rec_ind in curs_inds (v_schema_name,v_table_name) loop
      v_columns:='(';
      for rec_collist in curs_icollist (v_schema_name,rec_ind.index_name) loop
          if v_columns != '(' then 
            v_columns:=v_columns||','; 
          end if;
          v_columns:=v_columns || rec_collist.column_name; 
        end loop;
        v_columns:=v_columns || ')';
      dbms_output.put_line(rpad(rec_ind.index_name,32) || chr(9) || 
                           rpad(rec_ind.uniqueness || ':' || rec_ind.index_type,20) || chr(9) ||
                           v_columns);
      n_exist:=n_exist+1;
    end loop;
    if n_exist = 0 then
      dbms_output.put_line('<none>');
    end if;

    -- triggers
    dbms_output.put_line(chr(10) || rpad('TRIGGER',32) || chr(9) ||
                                    rpad('TYPE',20) || chr(9) ||
                                    rpad('EVENT',20) );
    dbms_output.put_line(rpad('-----',32) || chr(9) ||
                         rpad('----',20) || chr(9) ||
                         rpad('------',20) );
    n_exist:=0;
    for rec_trig in curs_trigs (v_schema_name,v_table_name) loop
      dbms_output.put_line(rpad(rec_trig.trigger_name,32) || chr(9) || 
                           rpad(rec_trig.trigger_type,20) || chr(9) ||
                           rpad(rec_trig.triggering_event,20) );
      n_exist:=n_exist+1;
    end loop;
    if n_exist = 0 then
      dbms_output.put_line('<none>');
    end if;

    -- some space before prompt
    dbms_output.put_line(chr(10));

  end if; -- check for table
end;
/

set feedback on
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s