Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

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? 

2 answers

0 votes

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).  

@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. 

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.

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.

@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. 

Suggest an answer

Log in or Sign up to answer
Community showcase
Published in Confluence

Confluence Mythbusters: Does Atlassian even use Confluence?

Hi, Confluence collaborators! As part of #Confluence-Collaboratory month, we’ve created a very special Mythsbusters segment, where we're dive into an interesting myth and uncover the truth behind i...

1,597 views 7 30
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you