Customized Plugins Query

August 15, 2010

 If you need to get a list of customized plugins out of an SLX database—for doing upgrades, etc.—the following query will be useful:

SELECT Family AS Family, [Name], ModifyDate, Company,
    CompanyVersion, Author, Family AS Family2,
    [Type] = CASE [Type]
	WHEN 0 THEN 'Process'
	WHEN 1 THEN 'Sales Process'
	WHEN 2 THEN 'Scripts, Basic (Legacy)'
	WHEN 3 THEN 'Views, Sales (Legacy)'
	WHEN 4 THEN 'Profiles'
	WHEN 5 THEN 'Scripts, SQL'
	WHEN 6 THEN 'System Reports'
	WHEN 7 THEN 'Templates, SalesLogix'
	WHEN 8 THEN 'Groups, Sales'
	WHEN 9 THEN 'Report Profiles'
	WHEN 10 THEN 'Reports, SalesLogix'
	WHEN 11 THEN 'SalesLogix WP templates'
	WHEN 12 THEN 'Templates, Word (2.x, 3.x, 4.x)'
	WHEN 13 THEN 'Macros'
	WHEN 14 THEN 'Strips, Menu'
	WHEN 15 THEN 'Strips, Toolbar'
	WHEN 16 THEN 'Nav Bars'
	WHEN 17 THEN 'Reports, IQ'
	WHEN 18 THEN 'Bitmaps'
	WHEN 19 THEN 'Reports, Crystal'
	WHEN 20 THEN 'Context Menus'
	WHEN 21 THEN 'SupportLogix Reports'
	WHEN 22 THEN 'Listviews, Support (Legacy)'
	WHEN 23 THEN 'Groups, Support'
	WHEN 24 THEN 'Detail Views, Support'
	WHEN 25 THEN 'Templates, Word (5.x)'
	WHEN 26 THEN 'XML Database Schema'
	WHEN 27 THEN 'VBScript'
	WHEN 28 THEN 'Active Forms'
	WHEN 29 THEN 'ActiveX Controls'
	WHEN 30 THEN 'MainViews'
	WHEN 31 THEN 'Global Scripts'
	WHEN 32 THEN 'Ticket'
	WHEN 33 THEN 'Contract'
	WHEN 34 THEN 'XML File'
	WHEN 37 THEN 'FileAttach'
	WHEN 38 THEN 'Text'
	END, Description
FROM sysdba.Plugin
WHERE Released = 'T'
AND Company <> 'SalesLogix'
AND Company <> 'Best Software'
AND Company <> 'Sage Software'
AND Company <> 'Sage'
AND Company <> 'Legacy'
-- and Type IN ('14', '15', '16', '27', '28', '30',
-- '31', '2', '3', '8', '23')
-- and Name NOT LIKE 'C[_]%'
-- underscore character is a wildcard in SQL
AND [Name]+Family IN (SELECT [Name]+Family 
     FROM sysdba.Plugin
     WHERE IsNull(Released, 'F') = 'F' 
     AND Company = 'SalesLogix')
     -- to return only plugins which are customized versions
     -- of OTB ones
ORDER BY [Type], Family, [Name]