How to get un-used webpart

Brijesh Singh asked on November 15, 2017 13:40

Hi Team,

I am trying to pull un-used webpart details from below query but looks like this is not 100% correct. Can you please assist me.

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 --SELECT CAST(CAST(YourXMLCol AS NVARCHAR(4000)) AS XML) CROSS APPLY (SELECT CAST(replace(PageTemplateWebParts,'&','amp;') 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 left outer JOIN View_CMS_Tree_Joined T ON T.NodeTemplateID = TemplateWebParts.PageTemplateID where T.NodeTemplateID is null ORDER BY NodeAliasPath

Correct Answer

Peter Mogilnitski answered on November 15, 2017 21:29

Kinda ugly and slow but it gives the idea, it gets web part name and the first template id where it is used:

select  webpartname, tp.PageTemplateId
 from CMS_WebPart wp
cross apply
(SELECT top 1  PageTemplateId
FROM cms_pagetemplate 
WHERE convert(xml, PageTemplateWebParts).exist('(/page/*/webpart[@type eq sql:column("webpartname")])') =1
) tp

I guess you need to run it ones (it is slow): so webparts that are not in this list are not in use.

1 votesVote for this answer Unmark Correct answer

Recent Answers

Brenden Kehren answered on November 15, 2017 14:39

Maybe try stripping all the garbage out of your query code and try posting it again. The query you posted is unreadable with the way you have posted and formatted it.

0 votesVote for this answer Mark as a Correct answer

Brijesh Singh answered on November 15, 2017 14:47

Apologies, not sure why it is showing like this. I have removed all commented lines .

SELECT DISTINCT WP.WebPartFileName,
NodeAliasPath,
PageTemplateCodeName
FROM CMS_WebPart WP INNER JOIN (

SELECT 
    PageTemplateID,
    PageTemplateCodeName,
    T.N.value('@type', 'varchar(50)') as WebPartName

CROSS APPLY (SELECT CAST(replace(PageTemplateWebParts,'&','amp;') AS XML)) as X(X)
CROSS APPLY X.X.nodes('/page/*/webpart') T(N)

) TemplateWebParts ON WP.WebPartName = TemplateWebParts.WebPartName

left outer JOIN View_CMS_Tree_Joined T ON T.NodeTemplateID = TemplateWebParts.PageTemplateID where T.NodeTemplateID is null ORDER BY NodeAliasPath

0 votesVote for this answer Mark as a Correct answer