CODEWITHSUNDEEP
kentico
Caveatrob
Caveatrob

Reputation: 13287

Kentico 7 DB Find Unused CMSWebparts

I have a site where a dev has created 30 copies of an ascx file and registered them as webparts. I would like to be able to query the Kentico database and find out which are live on the site and associate a web part with a filename. In other words, run a query to associate an ascx file with active pages.

One query I do shows me this in XML embedded in a table:

Where is this guid stored in the database? I'm missing an association here and it's driving me nuts. I can't determine from CMS Desk which web ascx file corresponds to each active page.

Upvotes: 0

Views: 97

Answers (1)

Kristian Bortnik
Kristian Bortnik

Reputation: 838

You could use the following query. It will list the path to the physical location of a WebPart ASCX file, the NodeAliasPath of the page it is used on, as well as the code name of the page template where the WebPart is located. You can tweak the selected columns to your liking.

SELECT DISTINCT
    WP.WebPartFileName,     -- Physical location of the webpart file
    NodeAliasPath,          -- Alias path of the page that uses the webpart
    PageTemplateCodeName    -- Code name of the template that contains the webpart
FROM CMS_WebPart WP
INNER JOIN
(
    -- Convert the PageTemplateWebParts column to XML
    -- Get the 'type' attribute from all 'webpart' elements, as the 'WebPartName' column
    SELECT 
        PageTemplateID,
        PageTemplateCodeName,
        T.N.value('@type', 'varchar(50)') as WebPartName
    FROM CMS_PageTemplate
    CROSS APPLY (SELECT CAST(PageTemplateWebParts AS XML)) as X(X)
    CROSS APPLY X.X.nodes('/page/*/webpart') T(N)
) TemplateWebParts ON WP.WebPartName = TemplateWebParts.WebPartName
-- Join the Tree view, to get NodeAliasPaths of pages that use the template
INNER JOIN View_CMS_Tree_Joined T ON T.NodeTemplateID = TemplateWebParts.PageTemplateID
ORDER BY NodeAliasPath

In Kentico, WebParts are located on page templates, which are then associated with pages. They can be found in the PageTemplateWebParts column as XML, alongside their settings.

The type attribute of the webpart element is the equivalent to the WebPartName column in the CMS_WebPart table.

Upvotes: 2

Related Questions