Today I had the request to get the owners of all existing communities. Based on the very helpful blog entry from Martijn de Jong I modified the SQL statement a little bit and received the needed informations in a very nice way.

Just connect to your SNCOMM database and run the following SQL command:

SELECT
SNCOMM.MEMBER.COMMUNITY_UUID AS COMMUNITY_UUID,
SNCOMM.COMMUNITY.NAME AS COMMUNITY_NAME,
SNCOMM.MEMBERPROFILE.DISPLAY AS MEMBER_NAME,
CASE SNCOMM.MEMBER.ROLE
WHEN 0 THEN ‘Member’
WHEN 1 THEN ‘Owner’
END AS MEMBER_ROLE
FROM SNCOMM.MEMBER
LEFT OUTER JOIN SNCOMM.MEMBERPROFILE ON SNCOMM.MEMBER.MEMBER_UUID = SNCOMM.MEMBERPROFILE.MEMBER_UUID
LEFT OUTER JOIN SNCOMM.COMMUNITY ON SNCOMM.COMMUNITY.COMMUNITY_UUID = SNCOMM.MEMBER.COMMUNITY_UUID
WHERE SNCOMM.MEMBERPROFILE.DISPLAY LIKE ‘%’ AND SNCOMM.MEMBER.ROLE=1
ORDER BY COMMUNITY_NAME ASC;