What kind of property do you use on your page, to reference the blocks? You'll need to parse that property's content.
Another approach would be using tblContentSoftLink. That table contains references to all content linked from your page.
This will give you a list of all content (not only blocks) that are referenced from the page with id = 4.
SELECT * FROM tblContentSoftLink WHERE fkOwnerContentID = 4
If all your blocks are of the same type, you could easily filter on ContentTypeId, if you join tblContent.
I'm interested why this needs to be a direct database query. I'd normally say that if you are having to directly craft your own queries against the Episerver database you're probably doing it wrong ;) Now, of course there will always be some sort of scenarios where this may be the only option. I'd just want to be sure its the last option to try rather than the first...
As you're finding:
Why not just use the Episerver API to get the data, which is trivial and then store the output directly in a new database table specific to the task. This could either be operated as a scheduled task or hooked into the content publishing events as necessary.
That way you'll still be able to use a database query to get the results, but you'll be able to use the Episerver API as intended and guard against breaking changes.
Thanks for the details Mark. I do understand that getting data directly from database is not a good option. We are creating a report to pull this data. However there has been some pressing need from client side for which they wanted to see if I can provide this information as one time activity to them while we simultaneously work on reports.
Using online articles I was able to understand part of database tables and was also able to pull the required data for a page type and all its properties and that gives me block guids but I wasn't sure on how to pull the acutal content.
I am still looking into the query Tomas gave and will update once I have got what i needed.
Thank you Tomas. Following your information and tying this data with tblProperty and tblPropertyDefinition tables to get the required property information I was able to get what I wanted.
I have a page type which has placeholder to hold list of blocks (of a specific type). These blocks have a property type xhtmlstring.
I need to create a database query to get content id, content name, the xhtmlstring from each block that is on the page.
I followed https://world.episerver.com/blogs/K-Khan-/Dates/2013/4/EpiServer-CMS-6-Useful-SQL-Queries/ and got an idea of the queries and was able to fetch all the properties and data from a page but can't find a way to get blocks data on the pages.
Any inputs on how I can form the query to get this data