If you want to list “Active HANA Ports” on OS or HANA Admin Tool via SQL, then you can try to use below SQL queries to do it easily ;

Architecture of HANA Ports ;

- We can list active “SQL Port” which is belongs to “SYSTEMDB” , to do this; Go on SYSTEMDB and execute following SQL query;

SELECT DATABASE_NAME, SQL_PORT FROM SYS_DATABASES.M_SERVICES WHERE DATABASE_NAME=’SYSTEMDB’
and SERVICE_NAME=’nameserver’  and COORDINATOR_TYPE= ‘MASTER’;

- We can list all the ports from SYS_DATABASES.M_SERVICES ,  to do this; Go on SYSTEMDB and execute following SQL query;

SELECT * FROM SYS_DATABASES.M_SERVICES;

Tenant DB Port number rule is ;

Example for an instance 02

- On Tenant DB , we can get “IndexServer” and “XSengine” Ports ;

SELECT SERVICE_NAME, PORT, SQL_PORT, (PORT + 2) HTTP_PORT FROM SYS.M_SERVICES WHERE ((SERVICE_NAME=’indexserver’ and COORDINATOR_TYPE= ‘MASTER’) or (SERVICE_NAME=’xsengine’));

- Checks all ports for a specific Tenant DB (example DB name is : H3) ;

SELECT DATABASE_NAME, SERVICE_NAME, PORT, SQL_PORT, (PORT + 2) HTTP_PORT FROM SYS_DATABASES.M_SERVICES WHERE DATABASE_NAME=’H3′and ((SERVICE_NAME=’indexserver’ and COORDINATOR_TYPE= ‘MASTER’) or (SERVICE_NAME=’xsengine’));

- Shows all Tenant DB’s all Port Numbers ;

SELECT DATABASE_NAME, SERVICE_NAME, PORT, SQL_PORT, (PORT + 2) HTTP_PORT FROM SYS_DATABASES.M_SERVICES WHERE ((SERVICE_NAME=’indexserver’ and COORDINATOR_TYPE= ‘MASTER’)or (SERVICE_NAME=’xsengine’));