How to find join indexes in database?

for newbie's there are various question that he may struck with, one of common question may be of these.

Join index can be found for a database by querying dbc.table.

select * from dbc.tables where databasename='mydatabasename' and tablekind='i';

OR

SELECT  * FROM dbc.indices 
WHERE indextype='J'
     AND databasename='mydatabase'
     AND tablename='mytablename'
ORDER BY indexname,columnposition;