In my Vendorization talk at the SyteLine User Network conference, I mentioned how simply poking around in SyteLine can sometimes cause unintended changes to Vendor objects. We discussed Vendorizing forms, but never really talked about global objects.
One of the things I have discovered recently is that sometimes during development, one could accidentally make a Site version of a global object without intending to. These global objects then sit as Site versions in the Forms Database, even though their contents are identical to the Vendor versions. (I find this tends to happen if you open up an object, like a Component Class, in the Winstudio editor, look at how it’s doing something, and then closing by clicking “OK” instead of “Cancel.”)
As I’ve mentioned before, every Site version is a liability. If you upgrade the Vendor version of the global object, and don’t FormSync it over, you will still be running the old version of this object.
So, what I have done is put together a little script that identifies non-Vendor objects in your forms database that are identical to the Vendor versions of those same objects. If you run it against your Forms database, any objects it returns should be safe to delete. (The code does not perform the delete, you’d have to do that in FormSync. Even still, I’d recommend checking them by using the “Compare” window in FormSync.)
Here’s the script, standard disclaimer: While this script is read-only, we are not responsible for any damage you do to your system with the results of this script. Take backups and work in a dev environment!
| /* | |
| Author: Jesse Brohinsky | |
| Company: Linemaster Switch Corporation | |
| Date: 2016-02-04 | |
| Comments: Checks non-vendor (Site, Group, and User) versions of global form objects, and compares the to Vendor version. | |
| If the Vendor version is the same as the Non-Vendor version, it is displayed below. These objects should be safe | |
| to delete. | |
| Parameters | |
| -------------------------------------------- | |
| @Post301Format | Sometimes Mongoose will set an AUTOIME(NoControl) value automatically, even though NULL is equivalent to | |
| NoControl. Set this parameter to 'AUTOIME(NoControl)' to include these as unmodified. Set this parameter | |
| to '' to not include them, or if you are unsure of how AUTOIME is supposed to work. | |
| */ | |
| DECLARE @Post301Format NVARCHAR(30) = 'AUTOIME(NoControl)'; | |
| WITH [VendorScriptLines] | |
| AS ( SELECT * | |
| FROM [ScriptLines] | |
| WHERE [ScriptLines].[ScopeType] = 0 | |
| ) , | |
| [NonVendorScriptLines] | |
| AS ( SELECT * | |
| FROM [ScriptLines] | |
| WHERE [ScriptLines].[ScopeType] <> 0 | |
| ) , | |
| [ModifiedScriptLines] | |
| AS ( SELECT [VendorScriptLines].[ScriptName] | |
| , [NonVendorScriptLines].[CodeLine] AS [SiteCodeLine] | |
| , [VendorScriptLines].[CodeLine] AS [VendorCodeLine] | |
| FROM [VendorScriptLines] | |
| INNER JOIN [NonVendorScriptLines] | |
| ON [VendorScriptLines].[ScriptName] = [NonVendorScriptLines].[ScriptName] | |
| AND [VendorScriptLines].[Sequence] = [NonVendorScriptLines].[Sequence] | |
| WHERE ISNULL([NonVendorScriptLines].[CodeLine], '') <> ISNULL([VendorScriptLines].[CodeLine], | |
| '') | |
| ) , | |
| [UnModifiedScript] | |
| AS ( SELECT DISTINCT | |
| [NonVendorScriptLines].[ScriptName] AS [Object] | |
| , 'Script' AS [Type] | |
| , CASE [NonVendorScriptLines].[ScopeType] | |
| WHEN 1 THEN 'Site' | |
| WHEN 2 THEN 'Group' | |
| WHEN 3 THEN 'User' | |
| END AS [ScopeType] | |
| , [NonVendorScriptLines].[ScopeName] | |
| FROM [NonVendorScriptLines] | |
| INNER JOIN [VendorScriptLines] | |
| ON [NonVendorScriptLines].[ScriptName] = [VendorScriptLines].[ScriptName] | |
| AND [NonVendorScriptLines].[Sequence] = [VendorScriptLines].[Sequence] | |
| WHERE NOT EXISTS ( SELECT 1 | |
| FROM [ModifiedScriptLines] | |
| WHERE [ModifiedScriptLines].[ScriptName] = [NonVendorScriptLines].[ScriptName] ) | |
| ) , | |
| [VendorPropertyDefaults] | |
| AS ( SELECT * | |
| FROM [PropertyDefaults] | |
| WHERE [PropertyDefaults].[ScopeType] = 0 | |
| ) , | |
| [NonVendorPropertyDefaults] | |
| AS ( SELECT * | |
| FROM [PropertyDefaults] | |
| WHERE [PropertyDefaults].[ScopeType] <> 0 | |
| ) , | |
| [UnModifiedPropertyDefaults] | |
| AS ( SELECT [NonVendorPropertyDefaults].[PropertyName] AS [Object] | |
| , CASE [NonVendorPropertyDefaults].[IsPropertyClassExtension] | |
| WHEN 1 THEN 'PropertyClassExtension' | |
| ELSE 'ComponentClass' | |
| END AS [Type] | |
| , CASE [NonVendorPropertyDefaults].[ScopeType] | |
| WHEN 1 THEN 'Site' | |
| WHEN 2 THEN 'Group' | |
| WHEN 3 THEN 'User' | |
| END AS [ScopeType] | |
| , [NonVendorPropertyDefaults].[ScopeName] | |
| FROM [NonVendorPropertyDefaults] | |
| INNER JOIN [VendorPropertyDefaults] | |
| ON [NonVendorPropertyDefaults].[PropertyName] = [VendorPropertyDefaults].[PropertyName] | |
| AND [NonVendorPropertyDefaults].[IsPropertyClassExtension] = [VendorPropertyDefaults].[IsPropertyClassExtension] | |
| WHERE ISNULL([NonVendorPropertyDefaults].[Flags], '') = ISNULL([VendorPropertyDefaults].[Flags], | |
| '') | |
| AND ISNULL([NonVendorPropertyDefaults].[ComponentFlags], | |
| '') = ISNULL([VendorPropertyDefaults].[ComponentFlags], | |
| '') | |
| AND ISNULL([NonVendorPropertyDefaults].[HelpContextID], | |
| '') = ISNULL([VendorPropertyDefaults].[HelpContextID], | |
| '') | |
| AND ISNULL([NonVendorPropertyDefaults].[Label], '') = ISNULL([VendorPropertyDefaults].[Label], | |
| '') | |
| AND ISNULL([NonVendorPropertyDefaults].[ListSource], '') = ISNULL([VendorPropertyDefaults].[ListSource], | |
| '') | |
| AND ISNULL([NonVendorPropertyDefaults].[FindFromForm], '') = ISNULL([VendorPropertyDefaults].[FindFromForm], | |
| '') | |
| AND ISNULL([NonVendorPropertyDefaults].[FindFromProperty], | |
| '') = ISNULL([VendorPropertyDefaults].[FindFromProperty], | |
| '') | |
| AND ISNULL([NonVendorPropertyDefaults].[MaintainFromSpec], | |
| '') = ISNULL([VendorPropertyDefaults].[MaintainFromSpec], | |
| '') | |
| AND ISNULL([NonVendorPropertyDefaults].[Validators], '') = ISNULL([VendorPropertyDefaults].[Validators], | |
| '') | |
| AND ISNULL([NonVendorPropertyDefaults].[MaxCharacters], | |
| '') = ISNULL([VendorPropertyDefaults].[MaxCharacters], | |
| '') | |
| AND ISNULL([NonVendorPropertyDefaults].[ValidateImmediately], | |
| '') = ISNULL([VendorPropertyDefaults].[ValidateImmediately], | |
| '') | |
| AND ISNULL([NonVendorPropertyDefaults].[ValueIsListIndex], | |
| '') = ISNULL([VendorPropertyDefaults].[ValueIsListIndex], | |
| '') | |
| AND ISNULL([NonVendorPropertyDefaults].[Description], '') = ISNULL([VendorPropertyDefaults].[Description], | |
| '') | |
| AND ISNULL([NonVendorPropertyDefaults].[DataType], '') = ISNULL([VendorPropertyDefaults].[DataType], | |
| '') | |
| AND ISNULL([NonVendorPropertyDefaults].[EventToGenerate], | |
| '') = ISNULL([VendorPropertyDefaults].[EventToGenerate], | |
| '') | |
| AND ISNULL([NonVendorPropertyDefaults].[SelectionEventToGenerate], | |
| '') = ISNULL([VendorPropertyDefaults].[SelectionEventToGenerate], | |
| '') | |
| AND ISNULL([NonVendorPropertyDefaults].[LoseFocusEventToGenerate], | |
| '') = ISNULL([VendorPropertyDefaults].[LoseFocusEventToGenerate], | |
| '') | |
| AND ISNULL([NonVendorPropertyDefaults].[GainFocusEventToGenerate], | |
| '') = ISNULL([VendorPropertyDefaults].[GainFocusEventToGenerate], | |
| '') | |
| AND ISNULL([NonVendorPropertyDefaults].[HelpString], '') = ISNULL([VendorPropertyDefaults].[HelpString], | |
| '') | |
| AND ISNULL([NonVendorPropertyDefaults].[HelpFileName], '') = ISNULL([VendorPropertyDefaults].[HelpFileName], | |
| '') | |
| AND ISNULL([NonVendorPropertyDefaults].[MenuName], '') = ISNULL([VendorPropertyDefaults].[MenuName], | |
| '') | |
| AND ISNULL([NonVendorPropertyDefaults].[Post301DataType], | |
| '') = ISNULL([VendorPropertyDefaults].[Post301DataType], | |
| '') | |
| AND ISNULL([NonVendorPropertyDefaults].[Post301Format], | |
| @Post301Format) = ISNULL([VendorPropertyDefaults].[Post301Format], | |
| @Post301Format) | |
| ) , | |
| [VendorStrings] | |
| AS ( SELECT * | |
| FROM [Strings] | |
| WHERE [Strings].[ScopeType] = 0 | |
| ) , | |
| [NonVendorStrings] | |
| AS ( SELECT * | |
| FROM [Strings] | |
| WHERE [Strings].[ScopeType] <> 0 | |
| ) , | |
| [UnModifiedStrings] | |
| AS ( SELECT [NonVendorStrings].[Name] AS [Object] | |
| , 'String' AS [Type] | |
| , CASE [NonVendorStrings].[ScopeType] | |
| WHEN 1 THEN 'Site' | |
| WHEN 2 THEN 'Group' | |
| WHEN 3 THEN 'User' | |
| END AS [ScopeType] | |
| , [NonVendorStrings].[ScopeName] | |
| FROM [NonVendorStrings] | |
| INNER JOIN [VendorStrings] | |
| ON [NonVendorStrings].[Name] = [VendorStrings].[Name] | |
| WHERE ISNULL([NonVendorStrings].[String], '') = ISNULL([VendorStrings].[String], | |
| '') | |
| AND ISNULL([NonVendorStrings].[String2], '') = ISNULL([VendorStrings].[String2], | |
| '') | |
| AND ISNULL([NonVendorStrings].[String3], '') = ISNULL([VendorStrings].[String3], | |
| '') | |
| ) , | |
| [VendorValidators] | |
| AS ( SELECT * | |
| FROM [Validators] | |
| WHERE [Validators].[ScopeType] = 0 | |
| ) , | |
| [NonVendorValidators] | |
| AS ( SELECT * | |
| FROM [Validators] | |
| WHERE [Validators].[ScopeType] <> 0 | |
| ) , | |
| [UnModifiedValidators] | |
| AS ( SELECT [NonVendorValidators].[Name] AS [Object] | |
| , 'Validator' AS [Type] | |
| , CASE [NonVendorValidators].[ScopeType] | |
| WHEN 1 THEN 'Site' | |
| WHEN 2 THEN 'Group' | |
| WHEN 3 THEN 'User' | |
| END AS [ScopeType] | |
| , [NonVendorValidators].[ScopeName] | |
| FROM [VendorValidators] | |
| INNER JOIN [NonVendorValidators] | |
| ON [VendorValidators].[Name] = [NonVendorValidators].[Name] | |
| WHERE ISNULL([NonVendorValidators].[Type], '') = ISNULL([VendorValidators].[Type], | |
| '') | |
| AND ISNULL([NonVendorValidators].[Parms], '') = ISNULL([VendorValidators].[Parms], | |
| '') | |
| AND ISNULL([NonVendorValidators].[Parms2], '') = ISNULL([VendorValidators].[Parms2], | |
| '') | |
| AND ISNULL([NonVendorValidators].[Parms3], '') = ISNULL([VendorValidators].[Parms3], | |
| '') | |
| AND ISNULL([NonVendorValidators].[ErrorMsg], '') = ISNULL([VendorValidators].[ErrorMsg], | |
| '') | |
| AND ISNULL([NonVendorValidators].[Description], '') = ISNULL([VendorValidators].[Description], | |
| '') | |
| ) , | |
| [VendorVariables] | |
| AS ( SELECT * | |
| FROM [Variables] | |
| WHERE [Variables].[ScopeType] = 0 | |
| ) , | |
| [NonVendorVariables] | |
| AS ( SELECT * | |
| FROM [Variables] | |
| WHERE [Variables].[ScopeType] <> 0 | |
| ) , | |
| [UnModifiedVariables] | |
| AS ( SELECT [NonVendorVariables].[Name] AS [Object] | |
| , 'Variable' AS [Type] | |
| , CASE [NonVendorVariables].[ScopeType] | |
| WHEN 1 THEN 'Site' | |
| WHEN 2 THEN 'Group' | |
| WHEN 3 THEN 'User' | |
| END AS [ScopeType] | |
| , [NonVendorVariables].[ScopeName] | |
| FROM [VendorVariables] | |
| INNER JOIN [NonVendorVariables] | |
| ON [NonVendorVariables].[Name] = [VendorVariables].[Name] | |
| AND [NonVendorVariables].[FormID] = [VendorVariables].[FormID] | |
| WHERE ISNULL([NonVendorVariables].[Value], '') = ISNULL([VendorVariables].[Value], | |
| '') | |
| AND ISNULL([NonVendorVariables].[Value2], '') = ISNULL([VendorVariables].[Value2], | |
| '') | |
| AND ISNULL([NonVendorVariables].[Value3], '') = ISNULL([VendorVariables].[Value3], | |
| '') | |
| AND ISNULL([NonVendorVariables].[Description], '') = ISNULL([VendorVariables].[Description], | |
| '') | |
| ) , | |
| [VendorMenus] | |
| AS ( SELECT * | |
| FROM [Menus] | |
| WHERE [Menus].[ScopeType] = 0 | |
| ) , | |
| [NonVendorMenus] | |
| AS ( SELECT * | |
| FROM [Menus] | |
| WHERE [Menus].[ScopeType] <> 0 | |
| ) , | |
| [ModifiedMenus] | |
| AS ( SELECT [NonVendorMenus].[Name] | |
| , [VendorMenus].[EventToGenerate] AS [VendorEventToGenerate] | |
| , [NonVendorMenus].[EventToGenerate] AS [SiteEventToGenerate] | |
| FROM [VendorMenus] | |
| INNER JOIN [NonVendorMenus] | |
| ON [NonVendorMenus].[Name] = [VendorMenus].[Name] | |
| AND [VendorMenus].[ItemName] = [NonVendorMenus].[ItemName] | |
| WHERE ISNULL([NonVendorMenus].[EventToGenerate], '') <> ISNULL([VendorMenus].[EventToGenerate], | |
| '') | |
| ) , | |
| [UnModifiedMenus] | |
| AS ( SELECT DISTINCT | |
| [NonVendorMenus].[Name] AS [Object] | |
| , 'Menu' AS [Type] | |
| , CASE [NonVendorMenus].[ScopeType] | |
| WHEN 1 THEN 'Site' | |
| WHEN 2 THEN 'Group' | |
| WHEN 3 THEN 'User' | |
| END AS [ScopeType] | |
| , [NonVendorMenus].[ScopeName] | |
| FROM [NonVendorMenus] | |
| INNER JOIN [VendorMenus] | |
| ON [NonVendorMenus].[Name] = [VendorMenus].[Name] | |
| AND [NonVendorMenus].[ItemName] = [VendorMenus].[ItemName] | |
| WHERE NOT EXISTS ( SELECT 1 | |
| FROM [ModifiedMenus] | |
| WHERE [ModifiedMenus].[Name] = [NonVendorMenus].[Name] ) | |
| ) | |
| SELECT [UnModifiedScript].[Object] | |
| , [UnModifiedScript].[Type] | |
| , [UnModifiedScript].[ScopeType] | |
| , [UnModifiedScript].[ScopeName] | |
| FROM [UnModifiedScript] | |
| UNION | |
| SELECT [UnModifiedPropertyDefaults].[Object] | |
| , [UnModifiedPropertyDefaults].[Type] | |
| , [UnModifiedPropertyDefaults].[ScopeType] | |
| , [UnModifiedPropertyDefaults].[ScopeName] | |
| FROM [UnModifiedPropertyDefaults] | |
| UNION | |
| SELECT [UnModifiedStrings].[Object] | |
| , [UnModifiedStrings].[Type] | |
| , [UnModifiedStrings].[ScopeType] | |
| , [UnModifiedStrings].[ScopeName] | |
| FROM [UnModifiedStrings] | |
| UNION | |
| SELECT [UnModifiedValidators].[Object] | |
| , [UnModifiedValidators].[Type] | |
| , [UnModifiedValidators].[ScopeType] | |
| , [UnModifiedValidators].[ScopeName] | |
| FROM [UnModifiedValidators] | |
| UNION | |
| SELECT [UnModifiedVariables].[Object] | |
| , [UnModifiedVariables].[Type] | |
| , [UnModifiedVariables].[ScopeType] | |
| , [UnModifiedVariables].[ScopeName] | |
| FROM [UnModifiedVariables] | |
| UNION | |
| SELECT [UnModifiedMenus].[Object] | |
| , [UnModifiedMenus].[Type] | |
| , [UnModifiedMenus].[ScopeType] | |
| , [UnModifiedMenus].[ScopeName] | |
| FROM [UnModifiedMenus] | |
| ORDER BY [Type], [Object], [ScopeType], [ScopeName]; |