Hi Fares,
This query should get you all the files in a course; including: the course files, files tied to blocks in the course, and files associated with resources in the course. I have it excluding automated backups, manual backups, user private files, and user draft files.
-- gets course union course modules union course blocks
SELECT course.id AS CourseID, course.fullname AS CourseFullName, course.shortname AS CourseShortName, course.filename, course.filesize AS CourseSizeBytes
FROM (
SELECT c.id, c.fullname, c.shortname, cx.contextlevel,f.component, f.filearea, f.filename, f.filesize
FROM mdl_context cx
JOIN mdl_course c ON cx.instanceid=c.id
JOIN mdl_files f ON cx.id=f.contextid
WHERE f.filename <> '.'
AND f.component NOT IN ("private", "automated", "backup","draft")
UNION
SELECT cm.course, c.fullname, c.shortname, cx.contextlevel,f.component, f.filearea, f.filename, f.filesize
FROM mdl_files f
JOIN mdl_context cx ON f.contextid = cx.id
JOIN mdl_course_modules cm ON cx.instanceid=cm.id
JOIN mdl_course c ON cm.course=c.id
WHERE filename <> '.'
UNION
SELECT c.id, c.shortname, c.fullname, cx.contextlevel, f.component, f.filearea, f.filename, f.filesize
from mdl_block_instances bi
join mdl_context cx on (cx.contextlevel=80 and bi.id = cx.instanceid)
join mdl_files f on (cx.id = f.contextid)
join mdl_context pcx on (bi.parentcontextid = pcx.id)
join mdl_course c on (pcx.instanceid = c.id)
where filename <> '.'
) AS course;
