EPiServer CMS 11 Useful SQL Queries - 2

Here is a set of few queries that we have been using in different investigations
  • Get usages of EPiServer contents including pages and blocks
  • Check Table size
  • No contents have been added for following content types
  • Looking into Activity Logs
  • Unmapped Property List


 Get usages of EPiServer contents including pages and blocks
SELECT
  tct.Name, 
  tct.ModelType, 
  COUNT(tc.pkID) AS PageCount
FROM
  tblContent AS tc RIGHT OUTER JOIN
  tblContentType AS tct ON tc.fkContentTypeID = tct.pkID
Where tct.ModelType is not null and tct.ModelType not like 'EPiServer.%'
GROUP BY
  tct.Name, tct.ModelType
ORDER BY
 PageCount desc

Check table size
EXEC sp_spaceused 'tblBigTable'

 No contents have been added for following content types
SELECT
  tct.Name, 
  tct.ModelType
FROM
  tblContent AS tc RIGHT OUTER JOIN
  tblContentType AS tct ON tc.fkContentTypeID = tct.pkID
Where tct.ModelType is not null and tct.ModelType not like 'EPiServer.%'
GROUP BY
  tct.Name, tct.ModelType

Having COUNT(tc.pkID) = 0

 Looking into Activity Logs
exec netActvitiyLogList @from='2018-02-01', @to='2019-02-14', @maxRows=10

 Unmapped Property List
SELECT tblContentProperty.LinkGuid AS GuidID, tblContentProperty.fkLanguageBranchID AS LanguageBranchID, tblPageDefinition.Name AS PropertyName, tblPageDefinition.fkPageTypeID AS PageTypeID, tblContentType.Name, 
                  tblContentType.ModelType
FROM     tblContentProperty INNER JOIN
                  tblPageDefinition ON tblContentProperty.fkPropertyDefinitionID = tblPageDefinition.pkID INNER JOIN
                  tblContent ON tblContentProperty.fkContentID = tblContent.pkID INNER JOIN
                  tblContentType ON tblContent.fkContentTypeID = tblContentType.pkID
WHERE  (tblContentProperty.LinkGuid IS NOT NULL) AND (tblContentProperty.ContentLink IS NULL)


Comments

Post a Comment

Popular posts from this blog

POC custom pricing provider works

In most ecommerce websites, prices comes from some third system and we import the prices into EPiServer Commerce along with products and variants data, but this is not the case all the time. In some scenarios pricing are quite complex and have to decide at run time or require to retrieve from some web services. A custom pricing provider can be built to support these kind of scenarios. we are require to implement IPriceService for read only prices and IPriceDetailService for Editing prices. We will be require to register these service in structure map e.g. services.AddSingleton< IPriceService, CustomPriceService >(); Find the POC on GIST
Read more

EPiServer CMS 11 Useful SQL Queries - 1

Here is a set of few queries that we have been using in different investigations Check How Big is your Database Get Data for Each Property and from Each Content Type Complete Tree Structure of your web site Check EPiServer DB Version Check How Big is Database SELECT      t.NAME AS TableName,     s.Name AS SchemaName,     p.rows AS RowCounts,     SUM(a.total_pages) * 8 AS TotalSpaceKB,      CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,     SUM(a.used_pages) * 8 AS UsedSpaceKB,      CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,      (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,     CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB FROM      sys.tables t INNER JOIN           sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN      sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INN
Read more