List all the tables of a Database 1

[marieannie]

Hello, all
1. Is there a simple way to list all the tables of a database?
2. I would like to display the complete structure of a table - I tried describe but doesn't show Primary key - Foreign key constraints...

Thank you for any suggestions...
MA

 

[SantaMufasa]

1. Is there a simple way to list all the tables of a database?

In Oracle, the term "database" refers to all of the users=schemas=owners and their objects that reside in an Oracle instance (installation). In non-Oracle environments, the term "database" refers to the objects of a single schema (and, as in Oracle, there can be many, many schemas.)


For your inquiry, then, I shall use the classic Oracle definition of "database". To show all of the tables of a database (to which the currently querying user can access), you can use this query:

select owner, table_name
  from all_tables;

2. I would like to display the complete structure of a table - I tried describe but doesn't show Primary key - Foreign key constraints...

Oracle's SQL*Plus has a "DESCRIBE <table>" comman that lists the structure of a <table>, but, as you have aptly pointed out, the "DESCRIBE" command ignores 3 1/2 of the 4 possible contraints on a table's columns: "DESCRIBE" shows "NOT NULL" constraints (a form of "CHECK" constraint), but ignores [ul][li]all other "CHECK" constraints[/li][li]"PRIMARY KEY" constraints,[/li][li]"UNIQUE" constraints, and
[/li][li]"FOREIGN KEY" constraints[/li][/ul]It is sad that Oracle has not properly enchanced their DESCRIBE feature to show the missing constraints.

I have, however, created such a feature, that simulates the DESCRIBE command, but shows explicitly the details of all constraints:[ul][li]Constraint type, i.e., [ul][li]"PK" - Primary Key constraint[/li][li]"FK" - Foreign Key constraint[/li][li]"UK" - Unique Key constraint[/li][li]"CK" - Check Constraint[/li][/ul][/li][li]Constraint Name[/li][li]Whether the constraint is "Enabled" or "Disabled", and[/li][li]Specifically, what the constraint enforces[/li][/ul]Following is a sample invocation and output of my "STRU.sql" script:

SQL> @stru
Enter table name: s_emp

Col Column             Data Type     [Constraint Type: Name: En-/Dis-abled]
 #  Name               and Length    and Enforcement
--- ------------------ ------------- -------------------------------------------
  1 ID                 NUMBER        [CK:S_EMP_ID_NN:ENABLED] "ID" IS NOT NULL
                                     [PK:S_EMP_ID_PK:ENABLED] NOT NULL/UNIQUE
  2 LAST_NAME          VARCHAR2(25)  [CK:S_EMP_LAST_NAME_NN:ENABLED] "LAST_NAME"
                                     IS NOT NULL

  3 FIRST_NAME         VARCHAR2(25)
  4 USERID             VARCHAR2(8)   [UK:S_EMP_USERID_UK:ENABLED] UNIQUE
  5 START_DATE         DATE
  6 COMMENTS           VARCHAR2(255)
  7 MANAGER_ID         NUMBER        [FK:S_EMP_MANAGER_ID_FK:ENABLED] Match :
                                     S_EMP_ID_PK

  8 TITLE              VARCHAR2(25)  [FK:S_EMP_TITLE_FK:ENABLED] Match :
                                     S_TITLE_TITLE_PK

  9 DEPT_ID            NUMBER        [FK:S_EMP_DEPT_ID_FK:ENABLED] Match :
                                     S_DEPT_ID_PK

 10 SALARY             NUMBER
 11 COMMISSION_PCT     NUMBER        [CK:S_EMP_COMMISSION_PCT_CK:ENABLED]
                                     commission_pct IN (10, 12.5, 15, 17.5, 20)

Another table (Y/N) ? 
********************************************************************************

To implement the above functionality, I provide two scripts:[ul][li]"STRUMAKE.sql" -- a one-time setup script that you run while connected to the Oracle user which you want to have this functionality and whose tables you wish to use the scripts against.[/li][li]"STRU.sql" -- the script that you use (after having run the one-time "STRUMAKE.sql" script) anytime you want to see the full structure and constraints of a table.[/li][/ul]Script 1 -- "STRUMAKE.sql":

REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor Dasages, LLC, makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
drop table temp_cons;
create table temp_cons as
select constraint_name, constraint_type, ' ' search_condition,
r_constraint_name, status
from user_constraints
where 1 = 2;
alter table temp_cons modify search_condition varchar(4000)
/
create or replace procedure prep_user_constraints (table_2_do in varchar)
is
   my_search_condition varchar(32767);
begin
   delete temp_cons;
   commit;
   for i in (select constraint_name
                   ,constraint_type
                   ,search_condition
                   ,r_constraint_name
                   ,status
                       from user_constraints
                      where table_name = upper(table_2_do)) loop
           my_search_condition := i.search_condition;
           insert into temp_cons values
                   (i.constraint_name
                   ,i.constraint_type
                   ,my_search_condition
                   ,i.r_constraint_name
                   ,i.status);
           commit;
   end loop;
end;
/

Script 2 -- "STRU.sql":

REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. The author
REM makes no warranty regarding this script's fitness for any
REM industrial application or purpose nor is there any claim that
REM this or any similarly-distributed scripts are error free or
REM should be used for any purpose other than illustration.
REM **************************************************************
set echo off
set verify off
set feedback off
set linesize 100
set pagesize 500
break on "#" on a on b
accept x prompt "Enter table name: "
col a format a18 heading "Column|Name" word_wrapped
col b format a13 heading "Data Type|and Length"
col c format a43 heading "[Constraint Type: Name: En-/Dis-abled]|and Enforcement" word_wrapped
col "#" format 99 heading "Col| # "
exec prep_user_constraints('&x')
(select column_id "#",
        col.column_name a,
        col.data_type ||
           decode(substr(data_type,1,4),
             'DATE',null,
             'LONG',null,
             'VARC','('||ltrim(to_char(data_length,'9999'))||')',
             'CHAR','('||ltrim(to_char(data_length,'999'))||')',
             'FLOA','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'9999')))||')'
                   ,null,
             'NUMB','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'999')))||')'
                   ,null)
           b,
       '[' ||
           decode(con.constraint_type,'P','PK','R','FK','C','CK','U','UK')
           || ':' ||con.constraint_name
           || ':' ||con.status
           || '] ' ||
           decode(con.constraint_type,
              'P','NOT NULL/UNIQUE',
              'U','UNIQUE',
              'C',con.search_condition,
              'R','Match : '||r_constraint_name) c
from   temp_cons con,
       user_cons_columns con_col,
       user_tab_columns col
where  col.table_name      = upper('&x')
  and  con.constraint_name = con_col.constraint_name
  and  col.table_name      = con_col.table_name
  and  col.column_name     = con_col.column_name
  )
union
((select column_id "#",
         col.column_name a,
         col.data_type ||
           decode(substr(data_type,1,4),
             'DATE',null,
             'LONG',null,
             'VARC','('||ltrim(to_char(data_length,'9999'))||')',
             'CHAR','('||ltrim(to_char(data_length,'999'))||')',
             'FLOA','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'9999')))||')'
                   ,null,
             'NUMB','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'999')))||')'
                   ,null)
           b,
        ' ' c
 from user_tab_columns col
 where col.table_name = upper('&x'))
minus
(select column_id "#",
        col.column_name a,
        col.data_type ||
           decode(substr(data_type,1,4),
             'DATE',null,
             'LONG',null,
             'VARC','('||ltrim(to_char(data_length,'9999'))||')',
             'CHAR','('||ltrim(to_char(data_length,'999'))||')',
             'FLOA','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'9999')))||')'
                   ,null,
             'NUMB','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'999')))||')'
                   ,null)
           b,
        ' ' c
 from user_tab_columns col, user_cons_columns con_col
 where col.table_name  = upper('&x')
   and col.table_name  = con_col.table_name
   and col.column_name = con_col.column_name)
)
order by 1
/
accept again prompt "Another table (Y/N) ? "
set heading off
set pagesize 0
spool temp.sql
set termout off
select decode(upper('&again'),'Y','@stru') from dual;
set termout on
spool off
set heading on
set pagesize 23
set feedback on
@temp

Let us know how "STRU.sql" works to resolve your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[marieannie]

Hello, SantaMufasa
Stru.sql works great. thank you for sharing it.

and Thank you for all your help.

MA

 

[SantaMufasa]

You are very welcome! And thanks for the little purple thangy.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]