The following query gives you an idea of all the moodle blocks that have been added to various pages on your site and where they are positioned:
select bi.*, bp.*, x.* from mdl_block_instances bi inner join mdl_block_positions bp on bi.id = bp.blockinstanceid inner join mdl_context x on x.id = bp.contextid order by bi.blockname;
Very handy if you have a block that is causing you problems e.g. not fully uninstalled or visible to users.
The pagetypepattern e.g.
*, site_index, my-index, course-view-* tells you which page types the block will be shown on, while the pagetype gives you the specific instances where it appears.
The context information at the end can also tell you the contextlevel (e.g. 50 = course, 70 = module) and the instance of that context (e.g. x.instanceid = 2 means course id 2 if the contextlevel is 50 (course context).