We're in the middle of a drupal to drupal migration of one of our content types (blogs) and a stakeholder pointed out that they have a large set of redirects for the contet types that will need to be migrated, which raises the question of how to find all the redirects. The easy thing to do is to list the redirects where the source is the node id of blogs published blogs.
select nid, dn.title, r.* from drupal_node as dn join drupal_path_redirect as r on dn.nid=substring(source, 6) where status=1 and dn.type='blog_post'
The problem is that redirects can be created where the source is an arbitrary string, like for instance, the url alias for a node. So the list above has to augmented with redirects where the source is the alias(s) of a blog.
select nid, da.src as alias_source, da.dst as alias_destination, r.source as redirect_source, r.redirect, r.type, from_unixtime(last_used) as last_redirect_use from drupal_node as dn join drupal_url_alias as da on substring(da.src,6)=dn.nid join drupal_path_redirect as r on da.dst = r.source where dn.status=1 and dn.type='blog_post'"
Even with that I dont have complete faith that I've found all the redirects, but I can't see a way to find anything that might be missing (at least, given that the redirects I'm interested in are configured in Drupal and not in .htacces or the CDN).