Views: 10264
Number of votes: 1
Average rating:

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

( 4/29/2013 11:23:14 AM)

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

K Khan
( By K Khan, 8/14/2013 11:12:21 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
)

( 10/21/2014 9:59:00 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
( By K Khan, 10/21/2014 11:31:45 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.