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' =
case
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
varchar(40))
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 ) )
end
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 sysobjects.id = syscolumns.id
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')
DROP PROCEDURE ShowHierarchy
go
CREATE PROC dbo.ShowHierarchy ( @Root int ) AS BEGIN
SET NOCOUNT ON
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)
WHILE @PageID IS NOT NULL
BEGIN
EXEC dbo.ShowHierarchy @PageID
SET @PageID = (SELECT MIN( pkID ) FROM dbo.tblPage
WHERE fkParentID = @Root AND pkID > @PageID)
END
END
go
ShowHierarchy 1
go

OUTPUT will be something like

--------Home
------------Browse catalogue
----------------Publications
--------------------Carousel
------------------------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