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)