I recently had an issue with a site, where (to cut a long story short), I had lost all of the the files on the hosting account.. core, modules, themes, etc...So I had to start a fresh with new install of Drupal.

I had the files for the theme, but not the modules... I normally use Backup & Migrate, but I couldn't do this until the module files were in place... So I had to do this first.

The database was still there on the hosting account, and I wondered if I could find a list of modules so I knew which ones I needed....

How do I get a list of modules, that are used on the site, from the database...?

The answer is in the 'syatem' table in the database... but the core modules, themes and other info is also in there, so I ended up using the following query...

SELECT `filename` 
FROM `system` 
WHERE `status` = 1
AND `type` = 'module'
AND `filename` LIKE 'sites/all/modules%'
ORDER BY `filename` ASC

`system` is the database table
`status` = 1 selects the active modules only
`type` = 'module' limits the results to just modules
LIKE 'sites/all/modules%' limits the module location, so it doesn't include the core modules

You should end up with a list, which is easy to read, and it lists the modules that were turned on in the 'sites/all/modules' folder... i.e.

Note, the highlighted module names... and where a module name is listed twice (as in 'admin_menu'), this is where a module has several parts to it (3 in the case of 'admin_menu')... it even shows the individual parts of the module that were ticked...