How to you write an sql statement that prints everything belonging to a particular space?

[P]

On this page, a query has been given to delete a page manually via SQL.  

How can this query be modified to print (instead of delete) everything belonging to a space given the space key? 

[Nic Brough -Adaptavist-]

With great difficulty, the data structure in Confluence is not what you expect it to be and you're going to need to think about dozens of queries in order to extract that.

The manual deletion thing you've found is nothing to do with reporting, it's there for when things are genuinely broken in the database and removing a page that way is the very last resort, and hence highly focussed on doing just a delete with minimal collateral damage.

Why are you trying to find everything that is in a space?  If that really is what you want for some reason, then why are you not simply using the space tools to get a list of pages and attachments (as those two are likely to be really what you're interested in).  

[P]

@Nic Brough -Adaptavist- - As mentioned in my reply to @Radek Dostál , we are trying to diagnose a problem happening only in a particular space and not in other spaces. So having a query that prints out what's in a space will be useful in doing a diff between good spaces and this one bad space. 

[Nic Brough -Adaptavist-]

Ok.  It probably won't be that useful, and more to the point, SQL is an awful way to try to get these lists out.

What exactly is the problem you are having?  It's very likely we can help you identify the cause and fix without torturing ourselves with database queries.

[Radek Dostál]

Why do you want to do this from SQL and for what purpose? That is one of the most painful methods you could possibly go for. There are space export options as well as content tools, besides the fact that page content will be stored in storage format in the database.

Moreover, what do you consider as "belonging to space" - are we talking about just pages, blogs, or are we also talking about space actors, notifications, subscriptions, attachment, labels and so forth, that's quite a lot of tables you would have to join.

[P]

@Radek Dostál - It will help us diagnose a problem with a particular space.  I am most interested in pages, blogs (and their previous versions/children),  and attachments (and previous versions) to those pages and blogs.

space actors, notifications, subscriptions, labels are not needed at the moment.