Finding nodes in Umbraco

in Software Engineering

As part of my day to day working with Umbraco I often find myself having to navigate through the Umbraco back office to setup data types, document types and content to make use of those types. Also, and in fact more often, I have to make use of existing data types, document types and content that have been setup some time in the distant past, occasionally in a very odd structure.

What I've found from doing this is that Umbraco hasn't made it very easy to find how all the connected parts are connected together. This is especially true in versions before v7. So I've ended up with many moments of feeling like I'm wandering round in circles in a maze whilst trying to find that specific content item that has used the document type I'm interested in or trying to find a particular document type in the tree of document types someone decided to create.

On one particular frustrating occasion I decided to dig into the database to find where a particular document type was used and found that it was actually far quicker to find what I was looking for.

So, off the back of that, I created the following SQL scripts to help look up some commonly required nodes:

Find content types by document type

Lists all Umbraco content items of the document type with the specified alias

DECLARE @DocumentTypeAlias NVARCHAR(MAX) = '**Document type alias**';

WITH ContentNodes (Id, Name, Path) AS  
(
    SELECT  N1.Id, 
            N1.Text,
            CAST(N1.Text AS NVARCHAR(MAX)) AS Path
    FROM    [umbracoNode] N1
    WHERE   N1.ParentId = -1
    AND     N1.Id >= 0
    UNION ALL
    SELECT  N2.Id, 
            N2.Text,
            CAST(N1.Path + '/' + N2.Text AS NVARCHAR(MAX)) AS Path
    FROM    [umbracoNode] N2
            INNER JOIN ContentNodes N1 ON N1.Id = N2.ParentId
)
SELECT    CN.Id,  
        CN.Name,
        CN.Path
FROM    [cmsContentType] CT  
        INNER JOIN [cmsContent] C ON CT.nodeId = C.contentType
        INNER JOIN ContentNodes CN ON C.nodeId = CN.Id
WHERE    CT.Alias = @DocumentTypeAlias;  

To use this simply replace **Document type alias** with the alias of the document type that the content types you are looking for use.

The data returned will look like this:

IdNamePath
1234Test Item/Home/Test Folder/Test Item
1235Test Item 2/Home/Test Folder/Test Item 2

Find document type path

Lists the node path of a document type with the specified alias

DECLARE @DocumentTypeAlias NVARCHAR(MAX) = '**Document type alias**';

WITH ContentNodes (Id, Name, Path) AS  
(
    SELECT  N1.Id, 
            N1.Text,
            CAST(N1.Text AS NVARCHAR(MAX)) AS Path
    FROM    [umbracoNode] N1
    WHERE   N1.ParentId = -1
    AND     N1.Id >= 0
    UNION ALL
    SELECT  N2.Id, 
            N2.Text,
            CAST(N1.Path + '/' + N2.Text AS NVARCHAR(MAX)) AS Path
    FROM    [umbracoNode] N2
            INNER JOIN ContentNodes N1 ON N1.Id = N2.ParentId
)
SELECT    CN.Id,  
        CN.Name,
        CN.Path
FROM    [cmsContentType] CT  
        INNER JOIN ContentNodes CN ON CT.nodeId = CN.Id
WHERE    CT.Alias = @DocumentTypeAlias;  

To use this simply replace **Document type alias** with the alias of the document type you are looking for.

The data returned will look like this:

IdNamePath
1234Test Doc Type/Home/Test Folder/Test Doc Type

Find document types that use data type

Lists all Umbraco document types that use the specified data type

DECLARE @DataTypeName NVARCHAR(MAX) = '**Data type name**';

WITH ContentNodes (Id, Name, Path) AS  
(
    SELECT  N1.Id, 
            N1.Text,
            CAST(N1.Text AS NVARCHAR(MAX)) AS Path
    FROM    [umbracoNode] N1
    WHERE   N1.ParentId = -1
    AND     N1.Id >= 0
    UNION ALL
    SELECT  N2.Id, 
            N2.Text,
            CAST(N1.Path + '/' + N2.Text AS NVARCHAR(MAX)) AS Path
    FROM    [umbracoNode] N2
            INNER JOIN ContentNodes N1 ON N1.Id = N2.ParentId
)
SELECT    CN.Id,  
        CN.Name,
        CN.Path
FROM    [umbracoNode] N  
        INNER JOIN [cmsDataType] DT ON DT.nodeId = N.Id
        INNER JOIN [cmsPropertyType] PT ON PT.dataTypeId = DT.nodeId
        INNER JOIN ContentNodes CN ON PT.contentTypeId = CN.Id
WHERE    N.Text = @DataTypeName;  

To use this simply replace **Data type name** with the name of the data type that the document types you are looking for use.

The data returned will look like this:

IdNamePath
1234Test Doc Type/Home/Test Folder/Test Doc Type
1235Test Doc Type 2/Home/Test Folder/Test Doc Type 2

Hope these scripts save you some time like they have for me.