How to list databases with their subscription/domain/owner using a SQL query in Plesk

Question

How to list databases with their subscription/domain/owner using a SQL query in Plesk?

Answer

1. Connect to a Plesk server via SSH/RDP.

2. Run one of these commands:

   • This SQL query lists all databases with corresponding subscriptions and customers.

     # plesk db "select db.name as 'Database',d.name as 'Subscription',c.pname as 'Owner',c.login as 'Customer Username' from data_bases db,domains d,clients c where d.cl_id=c.id and db.dom_id=d.id"

     The output will look as follows:

     +----------------+---------------+---------------+----------------+
     | Database       | Subscription  | Owner         | Owner Username |
     +----------------+---------------+---------------+----------------+
     | joomla_7       | joomla.tld    | Administrator | admin          |
     | wordpress_a    | wordpress.tld | Administrator | admin          |
     | db_example.com | example.com   | John Doe      | jdoe           |
     | db_example.net | example.com   | John Doe      | jdoe           |
     +----------------+---------------+---------------+----------------+

      

   • This SQL query lists all databases that have the option "Related to" defined in Plesk. If it is not set, database will not appear in the list. Please note that this query does not list databases that were created together with applications via the Plesk Applications menu.

     # plesk db "select db.name as 'Database', d.name as 'Related to' from data_bases db, domains d, dom_param p where d.id=p.dom_id and p.val=db.id and p.param='lastDatabaseSelectedId'"

     The output will look as follows:

     +----------------+-------------+
     | Database       | Related to  |
     +----------------+-------------+
     | db_example.com | example.com |
     | db_example.net | example.net |
     +----------------+-------------+