Helpful SQL Queries for Optimizely CMS 11

Optimizely (formerly Episerver) has a fairly complex database structure.  Here are some queries that I use often.  These are taken from Optimizely CMS 11 but should work in other versions as well.

GET PAGE TEMPLATES AND THEIR ASSOCIATED PAGES

;WITH cte AS (

	SELECT 
		o.pkID,
		y.Name AS Template,
		l.Name,
		l.URLSegment,
		y.ModelType, 
		CAST(l.URLSegment AS NVARCHAR(MAX)) AS Path,
		l.LinkUrl
	FROM 
		dbo.tblContent o inner join 
		dbo.tblContentLanguage l on o.pkID = l.fkContentID INNER JOIN
        dbo.tblContentType y ON y.pkID = o.fkContentTypeID
	WHERE 
		o.fkParentID IS NULL
		AND y.Base = 'page'

	UNION ALL

	SELECT
		o.pkID,
		y.Name AS Template,
		l.Name,
		l.URLSegment,
		y.ModelType, 
		(Path + '/' + CAST(l.URLSegment AS NVARCHAR(MAX))) AS Path,
		l.LinkUrl
	FROM 
		dbo.tblContent o inner join 
		dbo.tblContentLanguage l on o.pkID = l.fkContentID INNER JOIN
        dbo.tblContentType y ON y.pkID = o.fkContentTypeID INNER join
		cte c ON c.pkId = o.fkParentId
	WHERE
		y.Base = 'page'
)
SELECT cte.pkID,
       cte.Template,
       cte.Name AS PageName,
       cte.URLSegment AS Slug,
       REPLACE(cte.Path, 'root', 'https://mywebsite.com') AS Url,
	   REPLACE(cte.LinkURL, '~', 'https://mywebsite.com') AS LinkUrl,
	   cte.ModelType
FROM cte 
ORDER BY template, Path

Copy

GET PAGE PROPERTIES

SELECT 
	y.Name AS 'Page Type Name',
    d.Name AS 'Property Name',
	p.Name AS 'Property Type'
FROM 
	dbo.tblPageType y INNER JOIN 
	dbo.tblPageDefinition d ON y.pkID = d.fkPageTypeID INNER JOIN
	dbo.tblPageDefinitionType p ON d.fkPageDefinitionTypeID = p.pkID
WHERE
	p.name = 'Content'
ORDER BY 
	y.Name,
    d.FieldOrder

Copy

GET THE DATABASE VERSION

declare @version int
exec @version = sp_DatabaseVersion
print @version