K Khan
Apr 25, 2013
  12492
(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' =
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

Apr 25, 2013

Comments

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]
GROUP BY [Name]
HAVING [Name] like '%.png' and COUNT([Name]) > 1

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

Oct 21, 2014 09:59 AM

Hi,
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
Translating Optimizely CMS 12 UI components

Optimizely CMS 12 have been out for a while now, but still some elements haven't been properly translated resulting in a GUI defaulting to english....

Eric Herlitz | Jan 26, 2023 | Syndicated blog

Image preview in Optimizely CMS12 all properties view

With these simple steps, you can now see an Image and its Metadata, including size and dimensions, when editing an Image property in Optimizely...

Tomas Hensrud Gulla | Jan 26, 2023 | Syndicated blog

Setting up the ImageEditor in Optimizely CMS 12

Setting up certain configurations on Opimizely CMS 12 differs quite a bit from prior versions of (Episerver CMS 11 and older). Here's a small guide...

Eric Herlitz | Jan 25, 2023 | Syndicated blog

Happy Hour Returning in February

Hi everyone! It's been a while and we're excited to resume our Happy Hour in February for more learning, sharing, connecting, relaxing, and just to...

Patrick Lam | Jan 24, 2023

Planned breaking change for Locale parameter in Content Graph

Content Graph, a new service which makes it possible to query content using GraphQL, will have a breaking change soon. The service is currently in...

Jonas Bergqvist | Jan 23, 2023

Optimizely Advanced ContentArea Render is Back!

If you have used EPiBootstrapArea package - then back in those days we had an opportunity to render Episerver ContentArea items with different...

valdis | Jan 23, 2023 | Syndicated blog