Identify Multicolumn Foreign key on TD 14

Database
Enthusiast

Identify Multicolumn Foreign key on TD 14

Hello,

How can i identify all the single and multicolumn which are foreign keys , meaning when implementing either Soft Ri or not.

Thank you.

4 REPLIES
Enthusiast

Re: Identify Multicolumn Foreign key on TD 14

Enthusiast

Re: Identify Multicolumn Foreign key on TD 14

Hello,

Thank you for your time and reply.

I have seen this post , but i wanted to avoid this solution . There are system tables which keep the information of foreign key (parent - child ) but the granularity is on single column, even if the foreign key is a multicolumn .

I wanted to see if with sql  ,i could achieve that.

Thanks.

Junior Contributor

Re: Identify Multicolumn Foreign key on TD 14

It's an easy task to provide the info about multicolumn FKs using the dbc views as each column of a  multicolumns FK is stored as a row in those views (similar to dbc.IndicesV).

Enthusiast

Re: Identify Multicolumn Foreign key on TD 14

 Hi,

Finally , i will conclude again , never work tired. Thank you all for your time and answers.

sel 
indexID
,childdb as DB_FK_
,childtable as tbl_fk_
,max(case when rn1 = 1 then trim(childkeycolumn) else '' end)||
max(case when rn1= 2 then ','||trim(childkeycolumn) else '' end)||
max(case when rn1 = 3 then ','||trim(childkeycolumn) else '' end)||
max(case when rn1 = 4 then ','||trim(childkeycolumn) else '' end)||
max(case when rn1 = 5 then ','||trim(childkeycolumn) else '' end)||
max(case when rn1 = 6 then ','||trim(childkeycolumn) else '' end) as FK_
,ParentDB as db_pk_
,ParentTable as tbl_pk_
,max(case when rn1 = 1 then trim(ParentKeyColumn) else '' end)||
max(case when rn1= 2 then ','||trim(ParentKeyColumn) else '' end)||
max(case when rn1= 3 then ','||trim(ParentKeyColumn) else '' end)||
max(case when rn1 = 4 then ','||trim(ParentKeyColumn) else '' end)||
max(case when rn1 = 5 then ','||trim(ParentKeyColumn) else '' end)||
max(case when rn1 = 6 then ','||trim(ParentKeyColumn) else '' end) as PK_
from
(
sel
row_number() over (partition by childdb,childtable,childdb,childtable,IndexID order by childdb,childtable,childdb,childtable,IndexID ) as rn1
,childdb
,childtable
,childkeycolumn
,ParentDB
,ParentTable
,ParentKeyColumn
,IndexID
from DBC.All_RI_ChildrenV
)x
group by 1,2,3,5,6