List database relations with keys for files/tables

dspdbr

-- Returns database relations with keys 
-- for SQL tables, the name is convert to the physical filename first.

-- Simply paste this gist into ACS SQL and run it to create the UDTF.

-- Note: I am using library QUSRSYS. I suggest you put it into your own tool library

-- It is a cool example how far you can go with SQL: Have fun - 
-- (C) Niels Liisberg 2023

-- This gist is distributed on an "as is" basis, without warranties 
-- or conditions of any kind, either express or implied.
------------------------------------------------------------------------------------------------------------------------------------------------------------
create or replace function qusrsys.dspdbr  (
    library char(10),
    file char(10) default null,
    table varchar(64) default null
)
returns table ( 
  library char(10) ,
  file char(10),
  keys varchar (256),
  unique char(1)
) 
set option output=*print, commit=*none, dbgview = *list
begin   

    
    if file is null then 
        select system_table_name 
        into file
        from systables 
        where system_table_schema = library 
        and  table_name = table; 
    end if; 
    
    return 
        with a as ( 
            Select a.* 
                from  qsys.qadbxref a 
                where a.dbxlib = library  and a.dbxfil = file 
            union all  
                Select a.* 
                from   qsys.qadbxref a 
                inner join qsys.QADBFDEP b 
                on     a.dbxlib = b.dbfldp 
                and    a.dbxfil = b.dbffdp 
                and    dbflib = library and  dbffil = file  
        )
        Select dbxlib, dbxfil , keys , DBXUNQ
        from a
        join lateral ( 
            Select listagg (rtrim (dbkfld) ,',')  within group (order by dbkpos)  as keys
            from qsys.QADBKFLD c 
            left join QADBIATR 
                on  dbifil = dbxfil
                and dbilib = dbxlib
                and dbifld = dbkfld 
            where dbklib = dbxlib 
            and dbkfil = dbxfil
        ) k on 1=1; 
    
end;

-- Test cases:
select * from table ( dspdbr ( 
        file =>    'ORDKFLP',
        library => 'NHODATA'
    ) 
);