jdesc.sql – A better describe for Oracle (version 1)

This script has been updated with new features and functionality. Please click here to see the latest version.

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.

I wrote this a long long time ago, so I’m guessing there might be some new things that might be worth including. If you find something please let me know.

Here is your standard Oracle describe result:

SQL> desc useful_table
Name                                                              Null?    Type
----------------------------------------------------------------- -------- --------------------------------------------
STREAM_ID                                                         NOT NULL NUMBER(9)
SETTOP_DEVICE_ADDR                                                NOT NULL NUMBER(13)
CHANGE_CNTL_NUM                                                   NOT NULL NUMBER(9)

With jdesc.sql you can get much more information:

SQL> @jdesc useful_table

USEFUL_TABLE
IOT=>NO
PARTITIONED=>NO
MVIEWED=>NO

Synonym: PUBLIC : USEFUL_TABLE

COLUMN                                  TYPE            NULLS           DEFAULT
------                                  ----            -----           -------
STREAM_ID                               NUMBER(9,0)     NOT NULL
SETTOP_DEVICE_ADDR                      NUMBER(13,0)    NOT NULL
CHANGE_CNTL_NUM                         NUMBER(9,0)     NOT NULL

CONSTRAINT                              TYPE            CRITERIA
----------                              ----            --------
UT_PK                                   Primary         (STREAM_ID,SETTOP_DEVICE_ADDR)
UT_ST_FK                                References      ANOTHER_TABLE(STREAM_ID)
SYS_C0017348                            Check           "STREAM_ID" IS NOT NULL
SYS_C0017349                            Check           "SETTOP_DEVICE_ADDR" IS NOT NULL
SYS_C0017350                            Check           "CHANGE_CNTL_NUM" IS NOT NULL

INDEX                                   TYPE                    COLUMNS
-----                                   ----                    -------
UT2_IDX                                 NONUNIQUE:NORMAL        (SETTOP_DEVICE_ADDR,STREAM_ID)

TRIGGER                                 TYPE                    EVENT
-----                                   ----                    ------
USEFUL_TABLE_UPD                        BEFORE EACH ROW         INSERT OR UPDATE

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

/*
Created 03/20/2006 Jed S. Walker
Notes :
       does not work on Oracle <9i due to Mview check
Not done :
       does not handle "" table names
*/

set serveroutput on size 1000000

set verify off
set feedback off

set pages 50 linesize 120

col owner format a20
col object_name format a35

declare
  n_exist        number;
  v_table_name   user_tables.table_name%type;
  v_type_atts    varchar2(200);
  cursor curs_syns (tabname_val varchar2)
  is select owner, synonym_name
     from all_synonyms
     where table_name = tabname_val
     order by owner, synonym_name;
  cursor curs_tcols (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 table_name = tabname_val
     order by column_id;
  v_lenprec  varchar2(20);
  cursor curs_cons (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 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 (consname_val varchar2)
  is select column_name
     from all_cons_columns
     where constraint_name = consname_val
     order by position;
  v_criteria   varchar2(2000);
  cursor curs_inds (tabname_val varchar2)
  is select index_name, index_type, uniqueness
     from all_indexes
     where table_name = tabname_val
     order by uniqueness desc, index_name;
  cursor curs_icollist (indname_val varchar2)
  is select column_name
     from all_ind_columns
     where index_name = indname_val
     order by column_position;
  v_columns   varchar2(2000);
  cursor curs_trigs (tabname_val varchar2)
  is select trigger_name, trigger_type, triggering_event
     from all_triggers
     where table_name = tabname_val
     order by trigger_type, triggering_event, trigger_name;
begin
  -- get name
  v_table_name:='&1';
  v_table_name:=upper(v_table_name);  
  -- verify table exists
  select count(1) into n_exist
  from all_tables
  where table_name = v_table_name;
  if n_exist = 0 then
    dbms_output.put_line('Table does not exist');
  else

    -- show table name and type attributes
    dbms_output.put_line(chr(10) || v_table_name);
    -- show table type attributes
    select decode(iot_type,'IOT','YES','NO') into v_temp
    from all_tables
    where table_name = v_table_name;
    v_type_atts:=v_type_atts || 'IOT=>' || v_temp || chr(10);
    select partitioned into v_temp
    from all_tables
    where table_name = v_table_name;
    v_type_atts:=v_type_atts || 'PARTITIONED=>' || v_temp || chr(10);
    select count(1) into n_exist
    from all_base_table_mviews
    where master = v_table_name;
    if n_exist = 0 then
      v_type_atts:=v_type_atts || 'MVIEWED=>NO' || chr(10);
    else
      v_type_atts:=v_type_atts || 'MVIEWED=>YES' || chr(10);
    end if;
    dbms_output.put_line(v_type_atts);

    -- show synonyms
    for rec_syn in curs_syns (v_table_name) loop
      dbms_output.put_line('Synonym: ' || rec_syn.owner || ' : ' || rec_syn.synonym_name);
    end loop;

    -- 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_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) ||
                         '--------');
    for rec_con in curs_cons (v_table_name) loop
      if rec_con.constraint_type in ('Primary','Unique') then
        v_criteria:='(';
       for rec_collist in curs_ccollist (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 constraint_name = rec_con.r_constraint_name;
        v_temp:='(';
        for rec_collist in curs_ccollist (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;
      dbms_output.put_line(rpad(rec_con.constraint_name,32) || chr(9) ||
                           rpad(rec_con.constraint_type,14) || chr(9) ||
                           v_criteria);
    end loop;

    -- 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) ||
                         '-------');
    for rec_ind in curs_inds (v_table_name) loop
      v_columns:='(';
      for rec_collist in curs_icollist (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);
    end loop;

    -- 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) );
    for rec_trig in curs_trigs (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) );
    end loop;

  end if; -- check for table
end;
/

set feedback on
Advertisements

7 responses to “jdesc.sql – A better describe for Oracle (version 1)

  1. Pingback: A better describe for Oracle | Jed's

  2. Deanna McMurray

    Hello, this looks like a much more useful tool than the generic describe. However, when I copied it into our Solaris Oracle server, I received the following error when I try to execute the script:
    SQL> @jdesc_1.sql ACCT_DD_ATTR_DOC
    ACCT_DD_ATTR_DOC
    declare
    *
    ERROR at line 1:
    ORA-01422: exact fetch returns more than requested number of rows
    ORA-06512: at line 70
    SQL> quit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
    Any ideas what the issue is? I copied it two or three times but always receive this error.
    Thanks in advance for your help. Would like to use this script.

    • Hi Deanna,

      Thank you. I rarely use the regular describe any more. You are probably logged in as a user that can see multiple copies of the table in other schemas (occasionally I’ll run this on one of our systems with 12 duplicate schemas and get that error because I’m logged in as a DBA.) Try logging into the schema in question and then running it. I have thought about writing one that takes as input the schema as well as the table, but just haven’t gotten around to it as it is easy enough to just access the schema itself.

  3. Deanna McMurray

    Thanks I will try that and will let you know the results….

  4. Deanna McMurray

    I tried to use ALTER SESSION SET CURRENT_SCHEMA = but this doesn’t work. I don’t have the passwords to log into the regular user accounts. I suppose I will have to get another programmer who has access to try this out. If you could change this to take the schema as input that would be great. All of my accounts have DBA authority.

  5. I’ll make a note to do that.

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