Archives of the TeradataForum

Message Posted: Mon, 18 Aug 2003 @ 22:17:40 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I put this monster query together to identify soft RI candidates for R5 from the data dictionary tables. RI constraints are not typically defined on data warehouses, and the data models may not reliably document the RI constraints (even if they did, there may be no easy way to procedurally extract the information). This query identifies all the tables in the target db 'live_data' having columns that match the UPI of another table within the same db. It generates alot of rows, but it's better than staring at an ER diagram. If you want to check NUPI tables (because you may not have included all the pkey columns in the PI), change di.uniqueflag = 'Y' to di.uniqueflag = 'N'. To limit the posting size I only show the query up to 3 UPI columns, I have the query extended this out to 7 columns, which should be waaay overkill, if anyone needs it.

select pi.databasename, pi.tablename PK_table, c1.tablename FK_table,
pi.nbr_cols UPI_columns, pi.col1, pi.col2, pi.col3, pi.col4, pi.col5,
pi.col6, pi.col7
from (
select  di.databasename, di.tablename, di.indexname,
max(di.columnposition) nbr_cols,
max (case when di.columnposition = 1 then di.columnname else '' end) col1,
max (case when di.columnposition = 2 then di.columnname else '' end) col2,
max (case when di.columnposition = 3 then di.columnname else '' end) col3,
max (case when di.columnposition = 4 then di.columnname else '' end) col4,
max (case when di.columnposition = 5 then di.columnname else '' end) col5,
max (case when di.columnposition = 6 then di.columnname else '' end) col6,
max (case when di.columnposition = 7 then di.columnname else '' end) col7
 from dbc.indices di, dbc.tables dt where di.databasename = dt.databasename
and di.tablename = dt.tablename and dt.tablekind = 'T'
 and di.indextype = 'P' and di.databasename = 'live_data'  and
di.uniqueflag = 'Y'
group by 1,2,3
) PI
inner join dbc.columns c1 on pi.databasename = c1.databasename and
pi.tablename <> c1.tablename and pi.col1 = c1.columnname
inner join dbc.tables t1 on c1.databasename = t1.databasename and
c1.tablename = t1.tablename and t1.tablekind = 'T'
where pi.nbr_cols = 1

union all -- 2
select pi.databasename, pi.tablename PI_table, c1.tablename SI_table,
pi.nbr_cols, pi.col1, pi.col2, pi.col3, pi.col4, pi.col5, pi.col6,
pi.col7
from (
select  di.databasename, di.tablename, di.indexname,
max(di.columnposition)
nbr_cols,
max (case when di.columnposition = 1 then di.columnname else '' end)
col1,
max (case when di.columnposition = 2 then di.columnname else '' end)
col2,
max (case when di.columnposition = 3 then di.columnname else '' end)
col3,
max (case when di.columnposition = 4 then di.columnname else '' end)
col4,
max (case when di.columnposition = 5 then di.columnname else '' end)
col5,
max (case when di.columnposition = 6 then di.columnname else '' end)
col6,
max (case when di.columnposition = 7 then di.columnname else '' end)
col7
from dbc.indices di, dbc.tables dt
 where di.databasename = dt.databasename and di.tablename = dt.tablename
and
dt.tablekind = 'T'
 and di.indextype = 'P' and di.databasename = 'live_data'  and
di.uniqueflag
= 'Y'
group by 1,2,3
) PI
inner join dbc.columns c1 on pi.databasename = c1.databasename and
pi.tablename <> c1.tablename and pi.col1 = c1.columnname
inner join dbc.columns c2 on pi.databasename = c2.databasename and
pi.tablename <> c2.tablename and pi.col2 = c2.columnname
inner join dbc.tables t1 on c1.databasename = t1.databasename and
c1.tablename = t1.tablename and t1.tablekind = 'T'
where c1.tablename = c2.tablename and pi.nbr_cols = 2

union all --3
select pi.databasename, pi.tablename PI_table, c1.tablename SI_table,
pi.nbr_cols, pi.col1, pi.col2, pi.col3, pi.col4, pi.col5, pi.col6,
pi.col7
from (
select  di.databasename, di.tablename, di.indexname,
max(di.columnposition)
nbr_cols,
max (case when di.columnposition = 1 then di.columnname else '' end)
col1,
max (case when di.columnposition = 2 then di.columnname else '' end)
col2,
max (case when di.columnposition = 3 then di.columnname else '' end)
col3,
max (case when di.columnposition = 4 then di.columnname else '' end)
col4,
max (case when di.columnposition = 5 then di.columnname else '' end)
col5,
max (case when di.columnposition = 6 then di.columnname else '' end)
col6,
max (case when di.columnposition = 7 then di.columnname else '' end)
col7
 from dbc.indices di, dbc.tables dt where di.databasename =
dt.databasename
and di.tablename = dt.tablename and dt.tablekind = 'T'
 and di.indextype = 'P' and di.databasename = 'live_data'  and
di.uniqueflag
= 'Y'
group by 1,2,3
) PI
inner join dbc.columns c1 on pi.databasename = c1.databasename and
pi.tablename <> c1.tablename and pi.col1 = c1.columnname
inner join dbc.columns c2 on pi.databasename = c2.databasename and
pi.tablename <> c2.tablename and pi.col2 = c2.columnname
inner join dbc.columns c3 on pi.databasename = c3.databasename and
pi.tablename <> c3.tablename and pi.col3 = c3.columnname
inner join dbc.tables t1 on c1.databasename = t1.databasename and
c1.tablename = t1.tablename and t1.tablekind = 'T'
where c1.tablename = c2.tablename and c2.tablename = c3.tablename and
pi.nbr_cols = 3
order by 1,2,3

	<Prev	Next>		<<First	<Prev	Next>	Last>>