Retrieve changed pages from EpiServer database with sql script


I'm wondering how I can get hold of the changed date for a published page with sql.

We are using EPiServer.CMS.Core.10.4.3, EPiServer.Find.Framework.12.4.3

In code we do the following:

var query = searchInstance.Search();

query = query.Filter(x => x.Changed.InRange(DateTime.MinValue, todaysDate.AddMonths(-10))).FilterForVisitor();

query = query.Filter(x => !x.Ancestors().Match(startPage.ArchivePage.ID.ToString()));


(IRemindable inherits IContent and has a property DateTime Changed)

Sep 29, 2017 13:32

Why do you need to do it in SQL?

Oct 02, 2017 22:14

The customer wants to receive a list of pages that hasn't been modified since a given date from production as a one time thing.

Oct 03, 2017 9:34

You could work something out starting with [tblContentLanguage]. It has the datetime columns you need.

Oct 03, 2017 13:48

Hi Angelica
I wrote this quick SQL query that returns the Content ID of pages matching your criteria.

It looks after a property called Changed, like you mentioned. If it is less than or equal to 10 months ago, it is returned.
I did not test it with language branches.

DECLARE @MinDate datetime
SET @MinDate = DATEADD(m, -10, GETDATE())
PRINT @MinDate

	FROM tblContent c
		c.ArchiveContentGUID IS NULL AND
		c.pkID IN
			(SELECT cp.fkContentID
			FROM tblContentProperty cp
			INNER JOIN tblPropertyDefinition pd ON pd.pkID = cp.fkPropertyDefinitionID
			WHERE pd.Name = 'Changed' AND cp.Date IS NOT NULL AND cp.Date <= @MinDate)
Oct 03, 2017 13:54


I will try to do that! :)

Oct 03, 2017 13:56
This topic was created over six months ago and has been resolved. If you have a similar question, please create a new topic and refer to this one.
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.