Select distribution method of the tables

–Current DB
select current_db db_id,
current_catalog db_name,
t.oid,
t.relname,
t.reldistmethod reldistmethodnamecode,
case t.reldistmethod
when 6003 then ‘CENTRAL_HOST’
when 6005 then ‘RANDOM’
when 6004 then rtrim(nvl(d1.attname||’,’, ”)||
nvl(d2.attname||’,’, ”)||
nvl(d3.attname||’,’, ”)||
nvl(d4.attname||’,’, ”),
‘,’
)
else ‘UNKNOWN’
end reldistmethodname
from _t_class t
left join _v_table_dist d1 on t.oid = d1.relid
left join _v_table_dist d2 on d1.relid = d2.relid and d1.distseqno = d2.distseqno+1
left join _v_table_dist d3 on d2.relid = d3.relid and d2.distseqno = d3.distseqno+1
left join _v_table_dist d4 on d3.relid = d4.relid and d3.distseqno = d4.distseqno+1
limit 10
–All DBs but has a bug. Works obly for current DB :-)
SELECT t.objdb,
t.database,
t.objid,
t.owner,
t.tablename,
t.reldistmethod reldistmethodcode,
CASE t.reldistmethod
WHEN 6003 THEN ‘CENTRAL_HOST’
WHEN 6005 THEN ‘RANDOM’
WHEN 6004 THEN RTRIM(NVL(d1.attname||’,’, ”)||
NVL(d2.attname||’,’, ”)||
NVL(d3.attname||’,’, ”)||
NVL(d4.attname||’,’, ”),
‘,’
)
ELSE ‘UNKNOWN’
END reldistmethodname
FROM _v_table_xdb t
LEFT JOIN _v_table_dist_map_xdb d1 ON t.objdb = d1.objdb AND t.owner = d1.owner AND t.tablename = d1.tablename
LEFT JOIN _v_table_dist_map_xdb d2 ON d1.objdb = d2.objdb AND d1.owner = d2.owner AND d1.tablename = d2.tablename AND d1.distseqno = d2.distseqno + 1
LEFT JOIN _v_table_dist_map_xdb d3 ON d2.objdb = d3.objdb AND d2.owner = d3.owner AND d2.tablename = d3.tablename AND d2.distseqno = d3.distseqno + 1
LEFT JOIN _v_table_dist_map_xdb d4 ON d3.objdb = d4.objdb AND d3.owner = d4.owner AND d3.tablename = d4.tablename AND d3.distseqno = d4.distseqno + 1
WHERE reldistmethodname NOT IN (‘CENTRAL_HOST’)
LIMIT 10