If you have a lot of code snippets you could use individual files, say from a dedicated folder. That sounds like the sanest solution.
For keeping everything in a single file, you could introduce markers and use :start-after:., like:
# doc.rst
SELECT works like this:
.. literal-include:: all.sql
:start-after: select-statement-simple
:end-before: ---
# all.sql
select-statement-simple
SELECT * FROM table
---
select-statement-advanced
…
---
I agree that this is non-optimal.
The doctree has all information you want, though, and if you spare the time to write a custom plugin it should be easy to achieve. Let's take a look at this example:
Is parsed as the following Docutils tree:
<literal_block classes="code sql" xml:space="preserve">
<inline classes="keyword">
<inline classes="operator">
<inline classes="keyword">
<inline classes="keyword">
So, definitely possible to write a builder that queries (doctree.traverse) all literal_blocks that have the sql class. I think it's hardly a missing feature in Sphinx, but just out of scope.
Cheers,
Robert