Liferay 6: SQL query to get non-instantiated portlets
This SQL query lets you know the list of custom portlets that are not-instantiated (that has no entry in portlet preferences table).
(I wrote this query for a MS SQL DB, if you want to use it for another DB there are a few changes to make)
It only checks for portlet ids that are not numbers (to exclude Liferay portlets).
This query is based on the fact that if a portlet is used, it has an entry in table PortletPreferences
.
select distinct all_portlets.portletId
from
(select portletId from Portlet where IsNumeric(portletId)<>1) all_portlets
left join
(
select distinct p.portletId
from
(select portletId, len(portletId) as len from Portlet where IsNumeric(portletId)<>1) p,
PortletPreferences pp
where left(pp.portletId, p.len) = p.portletId
) used_portlets
on all_portlets.portletId = used_portlets.portletId
where used_portlets.portletId is null;
You can have the list of portlets that are instantiated by changing the last line of the query from
where used_portlets.portletId is null
to
where used_portlets.portletId is not null