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:

• The CMS database schema is 'deliberately' undocumented
• The schema is highly normalised, and so producing efficient queries can be difficult
• Episerver makes no guarantees about how they store their data. That is in effect not part of the pulic API, so any product updates, even minor could introduce breaking changes to your approach / solution that you'd be unaware of 

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.