Home » SQL & PL/SQL » SQL & PL/SQL » display columns names from all_tab_columns with primary key on top rows
display columns names from all_tab_columns with primary key on top rows [message #429698] Thu, 05 November 2009 05:27 Go to next message
mohan1760
Messages: 59
Registered: June 2008
Member
I want to display columns names from all_tab_columns with primary keys on top i.e. top records should be column names which are participating in primary keys..
Re: display columns names from all_tab_columns with primary key on top rows [message #429700 is a reply to message #429698] Thu, 05 November 2009 05:32 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
USER_CONSTRAINTS
Re: display columns names from all_tab_columns with primary key on top rows [message #429702 is a reply to message #429700] Thu, 05 November 2009 05:33 Go to previous messageGo to next message
mohan1760
Messages: 59
Registered: June 2008
Member
i tried that but i want list all columns among those columns primary key columns on top
Re: display columns names from all_tab_columns with primary key on top rows [message #429704 is a reply to message #429700] Thu, 05 November 2009 05:36 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Its_me_ved wrote on Thu, 05 November 2009 11:32
USER_CONSTRAINTS

It'll need a little more than that:
ALL_CONSTRAINTS and ALL_CONS_COLUMNS
Re: display columns names from all_tab_columns with primary key on top rows [message #429707 is a reply to message #429704] Thu, 05 November 2009 05:40 Go to previous messageGo to next message
mohan1760
Messages: 59
Registered: June 2008
Member
SEQ_NUMBER primary key
WEIGHT_BREAK primary key
LOOSE_RATE Optional
BASIC_CHARGE Optional
LAST_UPDATED_USER_ID Optional
LAST_UPDATED_TIMESTAMP Optional
FK_T_TONNAGE_RATONNAGE_TIER Optional
FK_T_TONNAGE_RASEQ_NUMBER Optional


this is what output should look like
Re: display columns names from all_tab_columns with primary key on top rows [message #429708 is a reply to message #429707] Thu, 05 November 2009 05:47 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Ok. Why not post what you have tried (you've been posting here long enough to know what is expected of you.)
Re: display columns names from all_tab_columns with primary key on top rows [message #429709 is a reply to message #429708] Thu, 05 November 2009 05:51 Go to previous messageGo to next message
mohan1760
Messages: 59
Registered: June 2008
Member
select decode(nullable,'Y','Optional','Mandatory') ,
a.COLUMN_NAME
from all_tab_columns a
where a.TABLE_NAME = 'T_TONNAGE_WEIGHT_BREAK'
Re: display columns names from all_tab_columns with primary key on top rows [message #429710 is a reply to message #429709] Thu, 05 November 2009 05:52 Go to previous messageGo to next message
mohan1760
Messages: 59
Registered: June 2008
Member
This is what i tried
it gives output but not as expected i.e. primary key column name on top
Re: display columns names from all_tab_columns with primary key on top rows [message #429711 is a reply to message #429709] Thu, 05 November 2009 05:53 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
And where is your attempt to incorporate the all_constraints and/or the all_cons_columns tables?
Why ask for advice if you are going to ignore it? (or do you just expect to be spoon fed a solution?)
Re: display columns names from all_tab_columns with primary key on top rows [message #429712 is a reply to message #429711] Thu, 05 November 2009 05:58 Go to previous messageGo to next message
mohan1760
Messages: 59
Registered: June 2008
Member
SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = 'T_TONNAGE_WEIGHT_BREAK'
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;

but it gives only primary columns how do i join those to get a ll columns
Re: display columns names from all_tab_columns with primary key on top rows [message #429713 is a reply to message #429712] Thu, 05 November 2009 06:02 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Are familiar with outer joins?
Re: display columns names from all_tab_columns with primary key on top rows [message #429714 is a reply to message #429709] Thu, 05 November 2009 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select decode(nullable,'Y','Optional','Mandatory'), a.COLUMN_NAME 
from all_tab_columns a, all_constraints b, all_cons_columns c
where a.TABLE_NAME = 'T_TONNAGE_WEIGHT_BREAK' 
  and a.owner = user
  and c.owner (+) = a.owner
  and c.table_name (+) = a.table_name
  and c.column_name (+) = a.column_name
  and b.owner (+) = c.owner
  and b.table_name (+) = c.table_name
  and b.constraint_type (+) = 'P'
order by decode(c.table_name,null,1,0), 
         nvl(c.position,a.column_id)
/

Regards
Michel
Re: display columns names from all_tab_columns with primary key on top rows [message #429715 is a reply to message #429704] Thu, 05 November 2009 06:07 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
pablolee wrote on Thu, 05 November 2009 05:36
Its_me_ved wrote on Thu, 05 November 2009 11:32
USER_CONSTRAINTS

It'll need a little more than that:
ALL_CONSTRAINTS and ALL_CONS_COLUMNS


Yes, it will need a little more than that Smile
Let the OP find how to map the columns for the tables found in %_CONSTRAINTS
Re: display columns names from all_tab_columns with primary key on top rows [message #429716 is a reply to message #429714] Thu, 05 November 2009 06:07 Go to previous messageGo to next message
mohan1760
Messages: 59
Registered: June 2008
Member
primary columns repeating
Re: display columns names from all_tab_columns with primary key on top rows [message #429718 is a reply to message #429716] Thu, 05 November 2009 06:29 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member

Quote:
primary columns repeating



Did you try the code given By Michel ?

SQL> create table emp_sriram as select * from emp;

Table created.


SQL> alter table emp_sriram add  constraint pk_empno primary key(sal,empno);

Table altered.

SQL> ed
Wrote file afiedt.buf

  1   select a.table_name||','||b.column_name||','||a.constraint_name||','||a.constraint_type||','||a.r_constraint_name||','||count(*)
  2   from user_constraints a,user_cons_columns b
  3   where a.OWNER=b.owner
  4   and a.CONSTRAINT_NAME=b.CONSTRAINT_NAME
  5   and a.table_name=b.table_name
  6  and a.table_name in('EMP_SRIRAM')
  7  and a.constraint_type in('P','R')
  8  and a.table_name not like 'BIN%'
  9  group by a.table_name,b.column_name,a.constraint_name,a.constraint_type,a.r_constraint_name
 10* order by a.table_name,a.constraint_type,a.r_constraint_name
 11  /

A.TABLE_NAME||','||B.COLUMN_NAME||','||A.CONSTRAINT_NAME||','||A.CONSTRAINT_TYPE
--------------------------------------------------------------------------------
EMP_SRIRAM,EMPNO,PK_EMPNO,P,,1
EMP_SRIRAM,SAL,PK_EMPNO,P,,1

SQL> ed
Wrote file afiedt.buf

  1  select decode(nullable,'Y','Optional','Mandatory'), a.COLUMN_NAME
  2  from all_tab_columns a, all_constraints b, all_cons_columns c
  3  where a.TABLE_NAME = 'EMP_SRIRAM'
  4    and a.owner = 'SCOTT'
  5    and c.owner (+) = a.owner
  6    and c.table_name (+) = a.table_name
  7    and c.column_name (+) = a.column_name
  8    and b.owner (+) = c.owner
  9    and b.table_name (+) = c.table_name
 10    and b.constraint_type (+) = 'P'
 11  order by decode(c.table_name,null,1,0),
 12*          nvl(c.position,a.column_id)
SQL> /

DECODE(NU COLUMN_NAME
--------- ------------------------------
Mandatory SAL
Mandatory EMPNO
Optional  ENAME
Optional  JOB
Optional  MGR
Optional  HIREDATE
Optional  COMM
Optional  DEPTNO

8 rows selected.

SQL> desc emp_sriram
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                       NOT NULL NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL>

[Updated on: Thu, 05 November 2009 06:31]

Report message to a moderator

Re: display columns names from all_tab_columns with primary key on top rows [message #429719 is a reply to message #429698] Thu, 05 November 2009 07:01 Go to previous message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
You may try this one

Very slow
select *
from all_tab_columns tc
	left outer join (
			all_cons_columns cc
			join all_constraints c on (
				c.owner=cc.owner 
				and c.constraint_name = cc.constraint_name 
				and c.constraint_type='P'
			)
		)on (
			tc.owner = cc.owner 
			and tc.table_name = cc.table_name 
			and tc.column_name = cc.column_name
		)
order by tc.table_name,cc.position nulls last,tc.column_id


or this faster one

with cons_columns as (
-------
-- Optimized redefinition of DBA_cons_columns in order to avoid 
-- additional redudant joins
-------
		select /*+inline*/u.name as owner,                                         -- owner
			c.name as constraint_name,                                              -- constraint_name
			decode(cd.type#,2,'PRIMARY KEY',3,'UNIQUE',4,'FOREIGN KEY') 
				as constraint_type,                                                  -- constraint_type
			o.name as table_name,                                                   -- table_name
			col.name as column_name,                                                -- column_name
			cc.pos# as position                                                     -- ordinal position
		from sys.con$ c
			join sys.user$ u on (c.owner# = u.user#)
			join sys.cdef$ cd on (c.con# = cd.con# and cd.type# != 12 /* don't include log groups */)
			join sys.ccol$ cc on (cd.con# = cc.con#)
			join sys.col$ col on (cc.obj# = col.obj# and cc.intcol# = col.intcol#)
			join sys.obj$ o on (cc.obj# = o.obj#)
	)
select *
from dba_tab_columns tc
	left outer join cons_columns cc on (
			tc.owner = cc.owner 
			and tc.table_name = cc.table_name 
			and tc.column_name = cc.column_name
		)
where cc.constraint_type = 'PRIMARY KEY'
order by tc.table_name,cc.position nulls last,tc.column_id




Bye Alessandro

[Updated on: Thu, 05 November 2009 07:12]

Report message to a moderator

Previous Topic: How to retrieve different data separated by comma into one column
Next Topic: oracle 9i and user activity logging
Goto Forum:
  


Current Time: Sat Oct 01 09:14:30 CDT 2016

Total time taken to generate the page: 0.14512 seconds