Skip to content

Instantly share code, notes, and snippets.

@leekelleher
Last active January 19, 2024 19:03
Show Gist options
  • Save leekelleher/7024860 to your computer and use it in GitHub Desktop.
Save leekelleher/7024860 to your computer and use it in GitHub Desktop.
Umbraco - database analysis - SQL Queries
-- Copied from Hendy https://our.umbraco.org/forum/umbraco-7/using-umbraco-7/72814-creating-a-list-of-unused-doc-types#comment-233729
-- Find all unused docTypes
-- These results may contain docTypes that are masters of used ones, so need to exclude these too...
SELECT
A.nodeId as 'DocTypeId',
B.text AS 'Name',
A.alias AS 'Alias'
FROM
cmsContentType A LEFT JOIN
umbracoNode B ON A.nodeId = B.id
WHERE
A.nodeId NOT IN (SELECT contentType FROM cmsContent)
;
SELECT
ContentId,
COUNT(ContentId) AS VersionCount
FROM
cmsContentVersion
GROUP BY
ContentId
ORDER BY
COUNT(ContentId) DESC
;
/* -- Version count joined with trashed/published state
SELECT
v.ContentId,
COUNT(v.ContentId) AS VersionCount,
n.trashed,
d.published
FROM
cmsContentVersion AS v
INNER JOIN umbracoNode AS n ON n.id = v.ContentId
INNER JOIN cmsDocument AS d ON d.nodeId = v.ContentId
WHERE
d.newest = 1
GROUP BY
v.ContentId,
n.trashed,
d.published
HAVING
COUNT(v.ContentId) > 25
ORDER BY
COUNT(v.ContentId) DESC,
v.ContentId ASC
;
*/
SELECT DISTINCT
pd.contentNodeId
,(CAST(pd.dataNvarchar AS INT) / 1048576.0) AS 'megaBytes'
,n2.createDate
,n2.path
,IIF(n2.path LIKE '-1,-21%', 'TRASHED', '') AS 'trashed'
,u.userName
,n.text
FROM
umbracoNode AS n
INNER JOIN cmsPropertyType AS pt ON n.id = pt.contentTypeId
INNER JOIN cmsPropertyData AS pd ON pt.id = pd.propertytypeid
INNER JOIN umbracoNode AS n2 ON pd.contentNodeId = n2.id
INNER JOIN umbracoUser AS u ON u.id = n2.nodeUser
WHERE
n.nodeObjectType = '4EA4382B-2F5A-4C2B-9587-AE9B3CF3602E'
AND pt.Alias = 'umbracoBytes'
AND CAST(pd.dataNvarchar AS INT) > 0
ORDER BY
'megaBytes' DESC,
pd.contentNodeId
;
SELECT
logHeader,
COUNT(logHeader) AS LogCount
FROM
umbracoLog
GROUP BY
logHeader
ORDER BY
COUNT(logHeader) DESC
;
SELECT
CASE
WHEN nodeObjectType = '7A333C54-6F43-40A4-86A2-18688DC7E532' THEN 'ContentItemType'
WHEN nodeObjectType = '10E2B09F-C28B-476D-B77A-AA686435E44A' THEN 'ContentItem'
WHEN nodeObjectType = '01BB7FF2-24DC-4C0C-95A2-C24EF72BBAC8' THEN 'ContentRecycleBin'
WHEN nodeObjectType = '30A2A501-1978-4DDB-A57B-F7EFED43BA3C' THEN 'DataType'
WHEN nodeObjectType = '521231E3-8B37-469C-9F9D-51AFC91FEB7B' THEN 'DataTypeContainer'
WHEN nodeObjectType = 'C66BA18E-EAF3-4CFF-8A22-41B16D66A972' THEN 'Document'
WHEN nodeObjectType = '6EBEF410-03AA-48CF-A792-E1C1CB087ACA' THEN 'DocumentBlueprint'
WHEN nodeObjectType = 'A2CB7800-F571-4787-9638-BC48539A0EFB' THEN 'DocumentType'
WHEN nodeObjectType = '2F7A2769-6B0B-4468-90DD-AF42D64F7F16' THEN 'DocumentTypeContainer'
WHEN nodeObjectType = 'CFED6CE4-9359-443E-9977-9956FEB1D867' THEN 'FormsDataSource'
WHEN nodeObjectType = 'F5A9F787-6593-46F0-B8FF-BFD9BCA9F6BB' THEN 'FormsForm'
WHEN nodeObjectType = '42D7BF9B-A362-4FEE-B45A-674D5C064B70' THEN 'FormsPreValue'
WHEN nodeObjectType = '87A9F1FF-B1E4-4A25-BABB-465A4A47EC41' THEN 'LockObject'
WHEN nodeObjectType = 'B796F64C-1F99-4FFB-B886-4BF4BC011A9C' THEN 'Media'
WHEN nodeObjectType = 'CF3D8E34-1C1C-41E9-AE56-878B57B32113' THEN 'MediaRecycleBin'
WHEN nodeObjectType = '4EA4382B-2F5A-4C2B-9587-AE9B3CF3602E' THEN 'MediaType'
WHEN nodeObjectType = '42AEF799-B288-4744-9B10-BE144B73CDC4' THEN 'MediaTypeContainer'
WHEN nodeObjectType = '39EB0F98-B348-42A1-8662-E7EB18487560' THEN 'Member'
WHEN nodeObjectType = '366E63B9-880F-4E13-A61C-98069B029728' THEN 'MemberGroup'
WHEN nodeObjectType = '9B5416FB-E72F-45A9-A07B-5A9A2709CE43' THEN 'MemberType'
WHEN nodeObjectType = 'B1988FAD-8675-4F47-915A-B3A602BC5D8D' THEN 'RelationType'
WHEN nodeObjectType = '9F68DA4F-A3A8-44C2-8226-DCBD125E4840' THEN 'Stylesheet'
WHEN nodeObjectType = '5555DA4F-A123-42B2-4488-DCDFB25E4111' THEN 'StylesheetProperty'
WHEN nodeObjectType = 'EA7D8624-4CFE-4578-A871-24AA946BF34D' THEN 'SystemRoot'
WHEN nodeObjectType = '6FBDE604-4178-42CE-A10B-8A2600A2F07D' THEN 'Template'
ELSE convert(nvarchar(50), nodeObjectType)
END AS objectType,
COUNT(nodeObjectType) AS nodeCount
FROM
umbracoNode
GROUP BY
nodeObjectType
HAVING
COUNT(nodeObjectType) > 1
ORDER BY
COUNT(nodeObjectType) DESC
;
SELECT
l.userId AS [userId],
u.userName AS [userName],
u.startStructureID AS [nodeId],
n.text AS [nodeName],
COUNT(u.id) AS [count]
FROM
umbracoLog AS l INNER JOIN
umbracoUser AS u ON l.userId = u.id INNER JOIN
umbracoNode AS n ON u.startStructureID = n.id
WHERE
l.NodeId > 0
AND l.logHeader = 'Publish'
AND l.userId > 0
AND u.startStructureID > 0
AND u.userType > 1
GROUP BY
l.userId,
u.userName,
u.startStructureID,
n.text
ORDER BY
COUNT(l.userId) DESC,
n.text ASC
;
@ProNotion
Copy link

Really handy Lee - thanks for sharing!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment