The following query provides a summary of the file space usage (in bytes) and number of files used by each course in the system from the
mdl_files meta table for the data directory. This is just for the space used in the data directory (not the database).
select f.contextid, x.instanceid, c.fullname as course_full_name, c.shortname as course_short_name, sum(f.filesize) as size_in_bytes, sum(case when (f.filesize > 0) then 1 else 0 end) as number_of_files from mdl_files f inner join mdl_context x on f.contextid = x.id and x.contextlevel = 50 inner join mdl_course c on c.id = x.instanceid group by f.contextid, x.instanceid order by sum(filesize) desc ;
It may need some refinement. Context level 50 refers to courses.