by jbrohinsky

Finding Customizations

When installing patches to SyteLine, one of the key tasks you should complete is determining if any of the affected objects you are applying interfere with customizations you have made. If you don’t have perfect documentation of all of your customizations, this script may help you. It pulls all objects that have been customized from the Forms, Objects, and Application databases, and also pulls report customizations as well from the Applications database. The script is currently configured to strip prefixes on Application DB/Report files, so that you can VLOOKUP the results with the files being applied from an APAR set. Feel free to modify as necessary.

/*
Author: Jesse Brohinsky
Company: Linemaster Switch Corporation
Date: 2017-02-23
Comments: Returns objects that have been customized. Removes prefixes for easier VLOOKUPs in Excel
Instructions: Find/Replace LSC_APP with your App database, LSC_FORMS with your Forms database, and
LSC_OBJECTS with your Objects database. Find/Replace LSC% with your company's prefix.
*/
SELECT [ModifiedObjects].[Name] AS [ObjectName]
, [ModifiedObjects].[Object] AS [ObjectType]
, CASE [ModifiedObjects].[ScopeType]
WHEN 1 THEN 'Site'
WHEN 2 THEN 'Group'
WHEN 3 THEN 'User'
END AS [ScopeType]
, [ModifiedObjects].[ScopeName] AS [User/Group Names]
FROM ( SELECT [Forms].[Name]
, [Forms].[ScopeType]
, [Forms].[ScopeName]
, 'Form' AS [Object]
FROM [LSC_FORMS].[dbo].[Forms]
WHERE [Forms].[ScopeType] > 0
UNION ALL
SELECT [Strings].[Name]
, [Strings].[ScopeType]
, [Strings].[ScopeName]
, 'String' AS [Object]
FROM [LSC_FORMS].[dbo].[Strings]
WHERE [Strings].[ScopeType] > 0
UNION ALL
SELECT [ActiveXScripts].[Name]
, [ActiveXScripts].[ScopeType]
, [ActiveXScripts].[ScopeName]
, 'Script' AS [Object]
FROM [LSC_FORMS].[dbo].[ActiveXScripts]
WHERE [ActiveXScripts].[ScopeType] > 0
UNION ALL
SELECT [PropertyDefaults].[PropertyName]
, [PropertyDefaults].[ScopeType]
, [PropertyDefaults].[ScopeName]
, 'Component Class' AS [Object]
FROM [LSC_FORMS].[dbo].[PropertyDefaults]
WHERE [PropertyDefaults].[ScopeType] > 0
AND [PropertyDefaults].[IsPropertyClassExtension] = 0
UNION ALL
SELECT [PropertyDefaults].[PropertyName]
, [PropertyDefaults].[ScopeType]
, [PropertyDefaults].[ScopeName]
, 'Property Class' AS [Object]
FROM [LSC_FORMS].[dbo].[PropertyDefaults]
WHERE [PropertyDefaults].[ScopeType] > 0
AND [PropertyDefaults].[IsPropertyClassExtension] = 1
UNION ALL
SELECT [Validators].[Name]
, [Validators].[ScopeType]
, [Validators].[ScopeName]
, 'Validator' AS [Object]
FROM [LSC_FORMS].[dbo].[Validators]
WHERE [Validators].[ScopeType] > 0
UNION ALL
SELECT [Variables].[Name]
, [Variables].[ScopeType]
, [Variables].[ScopeName]
, 'Variable' AS [Object]
FROM [LSC_FORMS].[dbo].[Variables]
WHERE [Variables].[ScopeType] > 0
UNION ALL
SELECT SUBSTRING([objects].[name],
PATINDEX('%[_]%', [objects].[name]) + 1, 255) AS [Name]
, 1 AS [ScopeType]
, '[NULL]' AS [ScopeNames]
, [objects].[type_desc] AS [Object]
FROM [LSC_APP].[sys].[objects]
WHERE ( [objects].[name] LIKE 'LSC%'
OR [objects].[name] LIKE 'EXTGEN%'
)
AND [objects].[type] IN ( 'P', 'TR', 'FN', 'U', 'V' )
UNION ALL
SELECT SUBSTRING([BGTaskDefinitions].[TaskExecutable],
PATINDEX('%[_]%',
[BGTaskDefinitions].[TaskExecutable]) + 1,
255) AS [Name]
, 1 AS [ScopeType]
, '[NULL]' AS [ScopeName]
, ISNULL([BGTaskDefinitions].[ReportType], 'SSRS') COLLATE DATABASE_DEFAULT AS [Object]
FROM [LSC_APP].[dbo].[BGTaskDefinitions]
WHERE [BGTaskDefinitions].[TaskExecutable] LIKE 'LSC%'
UNION ALL
SELECT [Collections].[Extends] AS [Name]
, 1 AS [ScopeType]
, '[NULL]' AS [ScopeName]
, 'IDO' COLLATE DATABASE_DEFAULT AS [Object]
FROM [LSC_OBJECTS].[dbo].[Collections]
WHERE [Collections].[CollectionName] LIKE 'LSC%'
AND [Collections].[Extends] IS NOT NULL
) [ModifiedObjects]
ORDER BY [ModifiedObjects].[Object] ASC
, [ModifiedObjects].[Name] ASC
, [ModifiedObjects].[ScopeType] ASC
, [ModifiedObjects].[ScopeName] ASC;
view raw Finding Customizations.sql hosted with ❤ by GitHub

Leave a comment