Try our conversational search powered by Generative AI!

K Khan
Apr 25, 2013
(1 votes)

EpiServer CMS 6 Useful SQL Queries

Here is a set of few useful queries that we have been using since CMS 5 internally for debugging and improving customer experience. I have not tested these with CMS 7.

List All Web Pages with Their Properties and Current Values

Below Query can help web editors to audit their contents.

select distinct tblPageLanguage.LinkURL 'Web Page URL', tblPageLanguage.Name 'Web Page Name',tblPageDefinition.Name
'Property Name', 'Property Value' =
when tblProperty.Number is null and tblProperty.FloatNumber is null and
tblProperty.PageType is null and tblProperty.PageLink is null and tblProperty.Date is null and
tblProperty.String is null and tblProperty.LongString is null
then 'Boolean: ' + cast( tblProperty.Boolean as varchar( 40 ) )
when tblProperty.Number is not null then 'Number: '+ cast( tblProperty.Number as varchar( 40 ) )
when tblProperty.FloatNumber is not null then 'FloatNumber: '+ cast( tblProperty.FloatNumber as
varchar( 40 ) )
when tblProperty.PageType is not null then 'PageType: '+ cast( tblProperty.PageType as
when tblProperty.PageLink is not null then 'PageLink: '+ cast( tblProperty.PageLink as
varchar( 40 ) )
when tblProperty.Date is not null then 'Date: '+ cast( tblProperty.Date as varchar( 40 ) )
when tblProperty.String is not null then 'String: '+ cast( tblProperty.String as varchar( 40 ) )
when tblProperty.LongString is not null then 'LongString: '+ cast( tblProperty.LongString as
varchar( 40 ) )
else cast( 'Error Determining Value!' as varchar( 40 ) )
from tblProperty
inner join tblPage on tblProperty.fkPageID = tblPage.pkID
inner join tblPageDefinition on tblProperty.fkPageDefinitionID = tblPageDefinition.pkID
inner join tblPageLanguage on tblpage.pkID = tblPageLanguage.fkPageID
order by tblPageLanguage.LinkURL, tblPageLanguage.Name, tblPageDefinition.Name

SQL Query to List All User Tables and Their Columns In a SQL Server Database

select sysobjects.Name, syscolumns.Name
from sysobjects inner join syscolumns on =
where sysobjects.xtype = 'U'
order by sysobjects.Name, syscolumns.colorder

SQL Server Procedure to Display the Web Page Hierarchy

It can help us to investigate large tree structures

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'ShowHierarchy' AND type = 'P')
CREATE PROC dbo.ShowHierarchy ( @Root int ) AS BEGIN
DECLARE @PageID int, @PageName varchar(30)
SET @PageName = (SELECT tblPageLanguage.Name FROM dbo.tblPage inner join tblPageLanguage on tblpage.pkID = tblPageLanguage.fkPageID WHERE pkID = @Root)
PRINT REPLICATE( '-', @@NESTLEVEL * 4) + @PageName
SET @PageID = (SELECT MIN( pkID ) FROM dbo.tblPage WHERE fkParentID = @Root)
EXEC dbo.ShowHierarchy @PageID
SET @PageID = (SELECT MIN( pkID ) FROM dbo.tblPage
WHERE fkParentID = @Root AND pkID > @PageID)
ShowHierarchy 1

OUTPUT will be something like

------------Browse catalogue
------------------------Publications Banner 1
------------------------Publications Banner 2
------------------------Publications Banner 3
------------------------Publications Banner 4
--------------------Featured Products
------------------------Product 3
------------------------Product 2
------------------------Product 1
--------------------Most Popular Products


List All Page Types and Their Properties

We used below query to make Properties Labels more readable for web editors.

select tblPageType.Name 'Page Type Name', tblPageType.Description 'Page Type Description',
tblPageType.FileName 'Page Template File', tblPageDefinition.Name 'Property Name',
tblPageDefinition.EditCaption 'Edit Heading', tblPageDefinition.HelpText 'Help Text'
from tblPageType inner join tblPageDefinition
on tblPageType.pkID = tblPageDefinition.fkPageTypeID
order by tblPageType.Name, tblPageDefinition.FieldOrder

List All Page Template Files, Page Types and Web Pages

select distinct tblPageType.FileName 'Page Template File', tblPageType.Name 'Page Type Name',
tblPageType.Description 'Page Type Description', tblPageLanguage.Name 'Web Page Name',
tblPageLanguage.LinkURL 'Web Page URL'
from tblPageType
inner join tblPage on tblPageType.pkID = tblPage.fkPageTypeID
inner join tblPageLanguage on tblPage.pkID = tblPageLanguage.fkPageID
order by tblPageLanguage.Name


List All Defined Page Types

select Name, Filename from tblPageType
order by Name

Apr 25, 2013


Apr 29, 2013 11:23 AM

This post goes directly to my "Good to have stuff"-folder! Thanks!

K Khan
K Khan Aug 14, 2013 11:12 AM

/*** Find all duplicates ***/
SELECT [Name], COUNT([Name])
FROM [tblItem]
HAVING [Name] like '%.png' and COUNT([Name]) > 1

/*** List all duplicates ***/
SELECT r.* FROM [tblRelation] AS r
Where r.ToName IN (
FROM [tblRelation]
HAVING [ToName] like '%.png' and COUNT([ToName]) > 1

Oct 21, 2014 09:59 AM

If I want to change a parent of a particular page from node1 to node2, which tables shall I look at?
Right now I have used the query below
update tblpage
set fkparentid = [new parent id]
where pkid = [id of the page]
This does change the node, but when I use the GetParents, it still points to the old node

K Khan
K Khan Oct 21, 2014 11:31 AM

Although I will not recommend a direct change in DB. rather use Mediachase functions.
In table (tblPage) you will see there are two internal relations ships. fkParentID and fkPageLinkId. columns fkParentID and pkID, is used to realise the page tree hierarchy. Every page in the page tree has a parent except for the page pointed to by EPiServer.Global.EPConfig.RootPage,

Please login to comment.
Latest blogs
From Procrastination to Proficiency: Navigating Your Journey to Web Experimentation Certification

Hey there, Optimizely enthusiasts!   Join me in celebrating a milestone – I'm officially a certified web experimentation expert! It's an exhilarati...

Silvio Pacitto | May 17, 2024

GPT-4o Now Available for Optimizely via the AI-Assistant plugin!

I am excited to announce that GPT-4o is now available for Optimizely users through the Epicweb AI-Assistant integration. This means you can leverag...

Luc Gosso (MVP) | May 17, 2024 | Syndicated blog

The downside of being too fast

Today when I was tracking down some changes, I came across this commit comment Who wrote this? Me, almost 5 years ago. I did have a chuckle in my...

Quan Mai | May 17, 2024 | Syndicated blog

Optimizely Forms: Safeguarding Your Data

With the rise of cyber threats and privacy concerns, safeguarding sensitive information has become a top priority for businesses across all...

K Khan | May 16, 2024