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

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

Strongly Type Models in EPiServer Commerce

We can create Strongly typed models just by inheriting a proper type from EPiServer.Commerce.Catalog.ContentTypes and and decorating it with the ContentTypeAttribute attribute. that means those types will be available as ContentData. To create models for commerce content following types are available. VariationContent : A type for variation/SKU models. ProductContent : A type for product models. BundleContent : A type for bundle models. PackageContent : A type for package models. DynamicPackageContent : A type for dynamic package models. NodeContent : A type for category/node models. Examples: using EPiServer.Commerce.Catalog.ContentTypes; using EPiServer.Commerce.Catalog.DataAnnotations; namespace EPiServer.Commerce.XYZ.Models.MetaDataClasses {     [CatalogContentType(GUID = "BE40A3E0-49BC-48DD-9C1D-819C2661C9BC" , MetaClassName = "Fashion_Item_Class" )]     public class FashionItemContent : VariationContent     {         public
Read more