There is no query that will identify all of the theme files that are actively in use, or not in use. The only query I'm aware of which will identify some theme files is:
SELECT * FROM wp_postmeta WHERE meta_key = '_wp_page_template';
which will identify all custom Page templates which are in use. It won't identify standard theme files such as index.php, single.php, header.php, footer.php, because those are not custom Page templates. And it's best practice to include index.php, since it is a default/fallback if there is ever a problem with more specific theme files. In many cases the file is never used, but it's always good to have it there, and it usually shows you the barebones HTML structure of the site, which can be a helpful cue before you dive into more customized files.
If you want to continue down the reverse-engineering path, I would suggest including your theme-file-identifier code in the (as a comment if it's a live site) and manually go through each URL. If you use a plugin that generates an XML sitemap it can help you ensure that you hit every URL. Just bear in mind that whatever file is identified is probably not the only file in use. For example, if your Posts use the default
single.php it is very likely to be making use of
footer.php at a minimum. Some themes use template parts or includes, so after you have your initial list of the overarching template used on each URL, you will have to look through each of those templates and determine which files they call. You'll also want to check
functions.php for the enqueued stylesheets and JS, as well as potentially other includes.
One alternative to this long process is to rebuild the theme from the ground up. I understand this isn't always possible, but it's the cleanest solution, and it's likely to take less time and be less risky than trying to slowly slough off parts of an old complex theme. For this process, I identify the most-used templates (if you have 50 of one CPT start there) and code those first on a dev/staging site, import or copy at least a handful of each post type and keep building from there. Once again you'll need to go through at least most of the site to make sure you don't overlook customizations, and the only way you'll be 100% certain you caught everything is to review every URL.