I managed to work it all out now. For anyone that is interested I used the code below:
/*************Books********************/
Select
mo.name as Type,
cat.name as Category,
course.fullname as Name,
book.name as NameContent,
chap.title as Content,
concat('%%WWWROOT%%/mod/book/view.php',CHAR(63),'id=',como.id,char(38),'chapterid=',chap.id) as URL
from prefix_course_modules como
join prefix_book book on como.instance = book.id
join prefix_book_chapters chap on book.id = chap.bookid
join prefix_modules mo on mo.id = como.module
join prefix_course course on book.course = course.id
join prefix_course_categories cat on course.category = cat.id
where chap.content like '%www.example.site%'
and mo.id = 3
union
/***********Questions***************/
Select
'Questions' as Type,
qcat.name as Category,
question.name as Name,
question.questiontext as NameContent,
'n/a' as Content,
'...' as Link
from prefix_question question
join prefix_question_categories qcat on question.category = qcat.id
where qcat.name like '%www.example.site%'
or question.questiontext like '%www.example.site%'
Union
/***********URL**********/
Select
mo.name,
cat.name as Category,
course.fullname as CourseName,
url.name as pageName,
'n/a',
concat('%%WWWROOT%%/mod/url/view.php',CHAR(63),'id=',como.id) as URL
from prefix_course_modules como
join prefix_url url on como.instance = url.id
join prefix_modules mo on mo.id = como.module
join prefix_course course on url.course = course.id
join prefix_course_categories cat on course.category = cat.id
where mo.id = 20
and url.intro like '%www.example.site%'
Union
/***********Quiz************/
Select
mo.name,
cat.name as Category,
course.fullname as CourseName,
quiz.name as quizName,
feedback.feedbacktext,
concat('%%WWWROOT%%/mod/page/view.php',CHAR(63),'id=',como.id) as quiz
from prefix_course_modules como
join prefix_quiz quiz on como.instance = quiz.id
join prefix_quiz_feedback feedback on quiz.id = feedback.quizid
join prefix_modules mo on mo.id = como.module
join prefix_course course on quiz.course = course.id
join prefix_course_categories cat on course.category = cat.id
where mo.id = 16
and (quiz.intro like '%www.example.site%' or feedback.feedbacktext like '%www.example.site%' )
Union
/***********Page************/
Select
mo.name,
cat.name as Category,
course.fullname as CourseName,
page.name as pageName,
'n/a',
concat('%%WWWROOT%%/mod/page/view.php',CHAR(63),'id=',como.id) as Page
from prefix_course_modules como
join prefix_page page on como.instance = page.id
join prefix_modules mo on mo.id = como.module
join prefix_course course on page.course = course.id
join prefix_course_categories cat on course.category = cat.id
where page.intro like '%www.example.site%'
and mo.id = 15
Union
/*******Labels********/
Select
mo.name,
cat.name as Category,
course.fullname as CourseName,
label.name as labelName,
'n/a',
concat('%%WWWROOT%%/mod/label/view.php',CHAR(63),'id=',como.id) as Label
from prefix_course_modules como
join prefix_label label on como.instance = label.id
join prefix_modules mo on mo.id = como.module
join prefix_course course on label.course = course.id
join prefix_course_categories cat on course.category = cat.id
where label.intro like '%www.example.site%'
and mo.id = 12
Union
/******Forums*******/
Select
mo.name,
cat.name as Category,
course.fullname as CourseName,
forum.name as ForumName,
'n/a',
concat('%%WWWROOT%%/mod/book/view.php',CHAR(63),'id=',como.id) as forum
from prefix_course_modules como
join prefix_forum forum on como.instance = forum.id
join prefix_modules mo on mo.id = como.module
join prefix_course course on forum.course = course.id
join prefix_course_categories cat on course.category = cat.id
where forum.intro like '%www.example.site%'
and mo.id = 9
Union
/********Glossary and entries*********/
Select
mo.name,
cat.name as Category,
course.fullname as CourseName,
glossary.name as GlossaryName,
entry.concept,
concat('%%WWWROOT%%/mod/glossary/showentry.php',CHAR(63),'courseid=',course.id,char(38),'eid=',entry.id,char(38),'displayformat=dictionary') as GlossaryEntry
from prefix_course_modules como
join prefix_glossary glossary on como.instance = glossary.id
join prefix_glossary_entries entry on glossary.id = entry.glossaryid
join prefix_modules mo on mo.id = como.module
join prefix_course course on glossary.course = course.id
join prefix_course_categories cat on course.category = cat.id
where mo.id = 10
and (glossary.intro like '%www.example.site%' or entry.definition like '%www.example.site%' )
