Oracle script

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Mon, 29 Oct 2007 6:44 am

Hey guys I found out, that there is a script that I can put in the Bank here and he returns the foreign keys of a table. I wonder if anyone has or knows of this script. Type how can I view foreign keys of a table in SQL server 2005 the script is this: set pagesize 200 col tabela_filha format a20 col tabela_mae format a20 col References format a20 col Chave_Primaria format a20
Code: Select all
select a.table_name tabela_filha, a.constraint_name Referencia,
       a.status, b.table_name tabela_mae, a.r_constraint_name Chave_Primaria
from user_constraints a, user_constraints b
where a.owner = b.owner and
      a.r_constraint_name = b.constraint_name and
      a.table_name = 'MIX0064'
union
select a.table_name tabela_filha, a.constraint_name Referencia,
       a.status, b.table_name tabela_mae, a.r_constraint_name Chave_Primaria
from user_constraints a, user_constraints b
where a.owner = b.owner and
      a.r_constraint_name = b.constraint_name and
      b.table_name = 'MIX064';
But in ORACLE does not work ask the help of vocês.
carlos12
Location: rio

Poston Mon, 29 Oct 2007 7:36 am

Oops, what's up??

You can consult the FK's of your table with this select here.

Code: Select all
SELECT a.table_name, a.constraint_name, c.column_name, b.table_name ref_table,
       d.column_name ref_col_name
  FROM user_constraints a,
       user_constraints b,
       user_cons_columns c,
       user_cons_columns d
WHERE b.constraint_name = d.constraint_name
   AND a.constraint_name = c.constraint_name
   AND a.r_constraint_name = b.constraint_name
   AND a.constraint_type = 'R'
   AND a.table_name = 'BAIRRO' -- nome da sua tabela.
Note: maybe this topic to be moved to another group forum.

[] 's
Tineks
Location: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Poston Mon, 29 Oct 2007 9:06 am

Topic moved to the SQL group.
Trevisolli
Location: Araraquara - SP

Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP



Return to SQL

Who is online

Users browsing this forum: No registered users and 2 guests