in 8 you still have views for each custom page type:
select DocumentName, * from View_[custom type table name]_Joined where [custom_field] = 'Y'
If we take articles type as example your view is called:
select * from View_CONTENT_Article_Joined
to find appropriate view
SELECT * FROM sys.views where name like '%atricle%' i.e. find me all views with article word in it.
P.S. To make without any view and work in any version you just do (if we use article type as example)
SELECT DocumentName,* from View_CMS_Tree_Joined
JOIN CONTENT_Article on DocumentForeignKeyValue = ArticleID and ClassName ='CMS.Article'
WHERE ArticleName = 'bla bla'
This above will work in any version