Umbraco: Find Document Types

nodes-of-document-type.sql

-- Find all umbraco nodes and the document type by the given document type id
-- Replace "XX_DOC_TYPE_ID_XX" with the document type id you want to look for
SELECT
    ct.[nodeId] as 'DocumentType ID',
    ct.[alias] as 'DocumentType Name',
    un.[id] as 'Node ID',
    un.[text] as 'Node Name',
    un.*
FROM [dbo].[cmsContentType] ct
    LEFT JOIN [dbo].[cmsContent] co
        ON co.[contentType] = ct.[nodeId]
    LEFT JOIN [dbo].[umbracoNode] un
        ON un.[id] = co.[nodeId]
WHERE
    ct.[nodeId] = XX_DOC_TYPE_ID_XX

unused-document-types.sql

-- Find all document types, that are
--  a) unused
--  b) only used on thrashed nodes
SELECT
    ct.[nodeId] as 'DocumentType ID',
    ct.[alias] as 'DocumentType Name',
    un.[id] as 'Node ID',
    un.[text] as 'Node Name'
FROM [dbo].[cmsContentType] ct
    LEFT JOIN [dbo].[cmsContent] co
        ON co.[contentType] = ct.[nodeId]
    LEFT JOIN [dbo].[umbracoNode] un
        ON un.[id] = co.[nodeId]
WHERE
    ct.[nodeId] NOT IN (
        SELECT DISTINCT
            ct.[nodeId]
        FROM [dbo].[cmsContentType] ct
            LEFT JOIN [dbo].[cmsContent] co
                ON co.[contentType] = ct.[nodeId]
            LEFT JOIN [dbo].[umbracoNode] un
                ON un.[id] = co.[nodeId]
        WHERE
            un.[trashed] = 0
    )