Select Banco Oracle - Constraint

Asked

Viewed 150 times

1

Good morning guys, if anyone can help me, I need a query that will return me the constraints of a table and its data types. I am trying to assemble a generator of Procedure to facilitate, it takes a long time to keep creating procedures. In this select does not bring me the type of data.

SELECT DISTINCT SYS.USER_CONS_COLUMNS.COLUMN_NAME FROM USER_CONSTRAINTS NATURAL JOIN SYS.USER_CONS_COLUMNS WHERE TABLE_NAME = 'PRODSERV';

oracle

  • Something like? http://stackoverflow.com/questions/1729996/list-of-foreign-keys-and-the-tables-they-reference

  • It seems to me that none of them brought me or that I need, for example I need the field type COD_UNID and its data type for example NUMBER, so I can create the parameters of Procedure. create or replace Procedure sp(SP_CODI_UNID number).....

  • o type da coluna se tem em USER_TAB_COLUMNS SELECT USER_CONS_COLUMNS.COLUMN_NAME , USER_TAB_COLUMNS.DATA_TYPE 
FROM USER_CONS_COLUMNS, USER_TAB_COLUMNS
WHERE USER_CONS_COLUMNS.TABLE_NAME = USER_TAB_COLUMNS.TABLE_NAME
AND USER_CONS_COLUMNS.COLUMN_NAME = USER_TAB_COLUMNS.COLUMN_NAME

  • Personnel I used as I had imagined, that and the same idea of Reginaldo Rigo, I used two queries and one is double, creating a new list only with the constraints...

  • Ulysses, the site works different from a forum, our "solved" is when you mark an answer as accepted, as you have already done, it already marks the question as "solved" :)

  • All right, thank you.

Show 1 more comment

1 answer

0


In a single query it is not possible, but in two or more it would be like this:

select COLUMN_NAME, DATA_TYPE from 
all_tab_columns where table_name = 'TABELA' 

SELECT constraint_name, constraint_type  
from user_constraints where table_name = 'TABELA'


SELECT constraint_name, constraint_type, column_name
from user_constraints natural join user_cons_columns
where table_name = 'TABELA'

Browser other questions tagged oracle

You are not signed in. Login or sign up in order to post.