Monday, June 26, 2006

Updated Dictionary Qrys 1

1.Primary Key details
======================
select
ac.TABLE_NAME,acc.constraint_name,
acc.column_name
from all_constraints ac,
all_cons_columns acc
where
ac.OWNER=acc.owner and
ac.TABLE_NAME=acc.table_name and
ac.CONSTRAINT_name=acc.CONSTRAINT_name and
ac.OWNER='' and ac.CONSTRAINT_TYPE='P'
order by ac.TABLE_NAME


2.Potential FK
================
select * from
(select p.TABLE_NAME,p.COLUMN_NAME
from all_tab_columns p
where
p.OWNER=''
minus
(select
x.TABLE_NAME,
--x.CONSTRAINT_NAME,
y.COLUMN_NAME
--,x.CONSTRAINT_TYPE
from all_constraints x,all_cons_columns y
where
x.OWNER=y.OWNER and
x.CONSTRAINT_NAME=y.CONSTRAINT_NAME and
x.TABLE_NAME=y.TABLE_NAME and
x.OWNER='' and x.CONSTRAINT_TYPE='R')) vw
where
vw.COLUMN_NAME like '%ID'
order by vw.TABLE_NAME;

-- or / and the one below:

-- should be compared for accuracy

select
a.TABLE_NAME,a.COLUMN_NAME
from all_tab_columns a
where
a.OWNER='' and
a.COLUMN_NAME like '%ID' and
a.COLUMN_NAME not in
(select
acc.column_name
from all_constraints ac,
all_cons_columns acc
where
ac.OWNER=acc.owner and
ac.TABLE_NAME=acc.table_name and
ac.CONSTRAINT_name=acc.CONSTRAINT_name and
ac.OWNER='' and ac.CONSTRAINT_TYPE in ('P','R'))
order by a.COLUMN_NAME


-- Then take the deatils of the existing FKs
-- put the results of both queries in the
-- same spreadshhet and then compare manually.


3. FK Related table details
============================
select
ac.TABLE_NAME,acc.COLUMN_NAME,ac.CONSTRAINT_NAME
from all_constraints ac,
all_cons_columns acc
where
ac.OWNER=acc.owner and
ac.TABLE_NAME=acc.table_name and
ac.CONSTRAINT_name=acc.CONSTRAINT_name and
ac.CONSTRAINT_NAME in
(select
ac.R_CONSTRAINT_NAME
from all_constraints ac,
all_cons_columns acc
where
ac.OWNER=acc.owner and
ac.TABLE_NAME=acc.table_name and
ac.CONSTRAINT_name=acc.CONSTRAINT_name and
ac.OWNER='' and ac.CONSTRAINT_TYPE='R')

--
/* FORMATTED ON 2006/06/26 13:19 (FORMATTER PLUS V4.8.5) */
SELECT *
FROM (SELECT AC.TABLE_NAME, AC.CONSTRAINT_NAME, ACC.COLUMN_NAME
FROM ALL_CONSTRAINTS AC, ALL_CONS_COLUMNS ACC
WHERE AC.OWNER = ACC.OWNER
AND AC.TABLE_NAME = ACC.TABLE_NAME
AND AC.CONSTRAINT_NAME = ACC.CONSTRAINT_NAME
AND AC.CONSTRAINT_NAME IN (
SELECT AC.R_CONSTRAINT_NAME
FROM ALL_CONSTRAINTS AC, ALL_CONS_COLUMNS ACC
WHERE AC.OWNER = ACC.OWNER
AND AC.TABLE_NAME = ACC.TABLE_NAME
AND AC.CONSTRAINT_NAME = ACC.CONSTRAINT_NAME
AND AC.OWNER = ''
AND AC.CONSTRAINT_TYPE = 'R')
UNION
SELECT AC.TABLE_NAME, ACC.CONSTRAINT_NAME, ACC.COLUMN_NAME
FROM ALL_CONSTRAINTS AC, ALL_CONS_COLUMNS ACC
WHERE AC.OWNER = ACC.OWNER
AND AC.TABLE_NAME = ACC.TABLE_NAME
AND AC.CONSTRAINT_NAME = ACC.CONSTRAINT_NAME
AND AC.OWNER = ''
AND AC.CONSTRAINT_TYPE = 'R') V
ORDER BY V.COLUMN_NAME

No comments:

Post a Comment