Umbraco - database analysis - SQL Queries

cmsContentType_Usage.sql

-- 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)
;

cmsContentVersion_Version_Count.sql

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
;
*/

cmsPropertyData-media-filesize.sql

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
;

umbracoLog_LogHeader_Count.sql

SELECT
	logHeader,
	COUNT(logHeader) AS LogCount
FROM
	umbracoLog
GROUP BY
	logHeader
ORDER BY
	COUNT(logHeader) DESC
;

umbracoNode_nodeObjectType_Count.sql

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
;

umbracoUser-most-active.sql

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
;

Really handy Lee - thanks for sharing!