SELECT 'cms_books_exists' AS metric, COUNT(*) AS value FROM information_schema.tables WHERE table_schema='book_search' AND table_name='cms_books'
UNION ALL
SELECT 'book_chapters_exists', COUNT(*) FROM information_schema.tables WHERE table_schema='book_search' AND table_name='book_chapters'
UNION ALL
SELECT 'products_book_id_col', COUNT(*) FROM information_schema.columns WHERE table_schema='book_search' AND table_name='products' AND column_name='book_id'
UNION ALL
SELECT 'products_book_content_id_col', COUNT(*) FROM information_schema.columns WHERE table_schema='book_search' AND table_name='products' AND column_name='book_content_id'
UNION ALL
SELECT 'cms_contents_book', COUNT(*) FROM cms_contents c INNER JOIN cms_content_types t ON t.id=c.content_type_id WHERE t.`key`='book'
UNION ALL
SELECT 'cms_contents_chapter', COUNT(*) FROM cms_contents c INNER JOIN cms_content_types t ON t.id=c.content_type_id WHERE t.`key`='chapter'
UNION ALL
SELECT 'book_chapter_relations', COUNT(*) FROM cms_content_relations WHERE relation_type='book_chapter'
UNION ALL
SELECT 'chapter_child_relations', COUNT(*) FROM cms_content_relations WHERE relation_type='chapter_child'
UNION ALL
SELECT 'products_book_content_id_nn', COUNT(*) FROM products WHERE book_content_id IS NOT NULL
UNION ALL
SELECT 'book_content_id_orphan', COUNT(*) FROM products p WHERE p.book_content_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM cms_contents c WHERE c.id=p.book_content_id)
UNION ALL
SELECT 'source_book_id_count', COUNT(*) FROM cms_content_meta WHERE meta_key='source_book_id'
UNION ALL
SELECT 'source_chapter_id_count', COUNT(*) FROM cms_content_meta WHERE meta_key='source_chapter_id'
UNION ALL
SELECT 'relation_orphan', COUNT(*) FROM cms_content_relations r WHERE NOT EXISTS (SELECT 1 FROM cms_contents c WHERE c.id=r.parent_content_id) OR NOT EXISTS (SELECT 1 FROM cms_contents c WHERE c.id=r.child_content_id);

SELECT 'fk_to_dropped' AS metric, COUNT(*) AS value
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA='book_search'
  AND REFERENCED_TABLE_NAME IN ('cms_books','book_chapters');

SELECT 'views_to_dropped' AS metric, COUNT(*) AS value
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA='book_search'
  AND (VIEW_DEFINITION LIKE '%cms_books%' OR VIEW_DEFINITION LIKE '%book_chapters%');

SELECT 'triggers_to_dropped' AS metric, COUNT(*) AS value
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA='book_search'
  AND (EVENT_OBJECT_TABLE IN ('cms_books','book_chapters') OR ACTION_STATEMENT LIKE '%cms_books%' OR ACTION_STATEMENT LIKE '%book_chapters%');

SELECT 'routines_to_dropped' AS metric, COUNT(*) AS value
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA='book_search'
  AND (ROUTINE_DEFINITION LIKE '%cms_books%' OR ROUTINE_DEFINITION LIKE '%book_chapters%');