Re: What is wrong ?

From: Tom Dyess <tdyess_at_dyessindustries.com>
Date: Fri, 01 Feb 2002 17:55:44 GMT
Message-ID: <AEA68.184491$_w.28805325@typhoon.tampabay.rr.com>

Ewww... There's query builder residue all over that SQL. JK. I ran it on a 9i Oracle database and it works fine for me.

SELECT DISTINCT ALL_CONS_COLUMNS.COLUMN_NAME,  ALL_TAB_COLUMNS.TABLE_NAME,
 ALL_TAB_COLUMNS.COLUMN_NAME,
 CON.CONSTRAINT_NAME CONSTRAINT_NAME, DECODE(CONSTRAINT_TYPE,'P','PRIMARY','R','FOREIGN','C','CHECK','V','VIEW','U ','UNIQUE')

FROM SYS.ALL_CONS_COLUMNS ALL_CONS_COLUMNS,
     SYS.ALL_TAB_COLUMNS ALL_TAB_COLUMNS,
     SYS.ALL_CONSTRAINTS CON

WHERE ALL_TAB_COLUMNS.TABLE_NAME = ALL_CONS_COLUMNS.TABLE_NAME (+)  AND ALL_TAB_COLUMNS.COLUMN_NAME = ALL_CONS_COLUMNS.COLUMN_NAME(+)  AND ALL_CONS_COLUMNS.CONSTRAINT_NAME = CON.CONSTRAINT_NAME ORDER BY table_name

Tom
www.oraclepower.com

"Josef" <einfach_gut_at_hotmail.com> wrote in message news:8b3ecf88.0202010637.68ebea0c_at_posting.google.com...
> Hi,
>
> i am trying to create select query. the query should include
> all columns of a table, table name, column_name of the constraint,
> constraint name,constraint type.
>
> some thing is wrong is in my query !!!!
>
> SELECT DISTINCT ALL_CONS_COLUMNS.COLUMN_NAME
> "ALL_CONS_COLUMNS.COLUMN_NAME",
> ALL_TAB_COLUMNS.TABLE_NAME "ALL_TAB_COLUMNS.TABLE_NAME",
> ALL_TAB_COLUMNS.COLUMN_NAME "ALL_TAB_COLUMNS.COLUMN_NAME",
> CON.CONSTRAINT_NAME CONSTRAINT_NAME,
> DECODE(CONSTRAINT_TYPE,'P','PRIMARY','R','FOREIGN','C','','V','','U','')
> FROM SYS.ALL_CONS_COLUMNS ALL_CONS_COLUMNS,
> SYS.ALL_TAB_COLUMNS ALL_TAB_COLUMNS,
> SYS.ALL_CONSTRAINTS CON
> WHERE ALL_TAB_COLUMNS.TABLE_NAME = ALL_CONS_COLUMNS.TABLE_NAME (+)
> AND ALL_TAB_COLUMNS.COLUMN_NAME = ALL_CONS_COLUMNS.COLUMN_NAME(+)
> AND ALL_CONS_COLUMNS.CONSTRAINT_NAME = CON.CONSTRAINT_NAME;
>
>
> can someone help please ?
>
Received on Fri Feb 01 2002 - 11:55:44 CST