learn a table's primary keys 1

[OhioSteve]

I want to learn the pk for a table in an existing Oracle DB. I tried "describe [domain].[name];". I learned alot about the table, but the pk was not listed.

The formal name for a key is a "constraint". So perhaps I need to say "table, tell me what constraints effect you..."

I look forward to your feedback.

 

[SimonSellick]

The script below queries all_constraints to list varous info about a table, including its primary key.

select    lower(c.table_name) "table"
,         lower(c.column_name) "column"
,         lower(case when data_precision is null then case when data_type = 'DATE' then data_type else data_type || '(' || data_length || ')' end else data_type || '(' || data_precision || '.' || nvl(data_scale, 0) || ')' end) "type"
,         lower(case when nullable = 'N' then 'NOT NULL' else null end) "nullable"
,         lower(case when p.position is not null then 'PK ' || p.position else null end) "p-k"
,         lower(case when f.refers is not null then '-> ' || f.refers else null end) "f-k"
from      all_tab_columns c
left join (select  table_name
					 ,       column_name
					 ,       position
					 from    all_cons_columns
					 where   constraint_name in (
					         select constraint_name 
					         from   all_constraints 
					         where  constraint_type = 'P'
					 )
          ) p on p.table_name = c.table_name and p.column_name = c.column_name
left join (select a.table_name
					 ,      c.column_name
					 ,      b.table_name || '.' || b.column_name refers
					 from   all_constraints a
					 join   all_cons_columns c 
					        on c.constraint_name = a.r_constraint_name 
					        and c.owner = a.owner
					 join   all_cons_columns b 
					        on b.constraint_name = a.r_constraint_name 
					        and b.owner = a.owner 
				 	        and b.position = c.position
          ) f on f.table_name = c.table_name and f.column_name = c.column_name
where     c.table_name not like '%$%'
and       c.table_name in (select table_name from user_tables)
order by  c.table_name
,         p.position
,         column_id

HTH

 

[SantaMufasa]

Here is an alternative script that produces formatted results for one table at a time, per "ACCEPT...PROMPT" responses from the user.

Section 1 -- Sample invocation and output (that describes Oracle Education's "S_EMP" table, including Primary Key and Foreign Key definitions):

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(7)     [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(7)     [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(7)     [FK:S_EMP_DEPT_ID_FK:ENABLED] Match :
                                     S_DEPT_ID_PK

 10 SALARY             NUMBER(11,2)

Col Column             Data Type     [Constraint Type: Name: En-/Dis-abled]
 #  Name               and Length    and Enforcement
--- ------------------ ------------- -------------------------------------------
 11 COMMISSION_PCT     NUMBER(4,2)   [CK:S_EMP_COMMISSION_PCT_CK:ENABLED]
                                     commission_pct IN (10, 12.5, 15, 17.5, 20)

Another table (Y/N) ?

Section 2 -- One-time-only script (save as script "StruMake.sql") that CREATEs a utility table and a procedure that the "always-run" script needs:

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(2000)
/
create or replace procedure prep_user_constraints (table_2_do in varchar)
  is
  cursor cons is
     select constraint_name,constraint_type,
            search_condition,r_constraint_name, status
            from user_constraints
            where table_name = upper(table_2_do);
  my_search_condition varchar(32767);
begin
  delete temp_cons;
  commit;
  for i in cons 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;

Section 3 -- Code that displays structure and constraints (Save as script "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 80
set pagesize 23
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'))||')',
             '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'))||')',
             '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'))||')',
             '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 if this solves your need.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]