Helpful upgrade tools

Use the following helpful tools to assist with iMIS upgrades.

Finding queries that reference specific business objects

Throughout the upgrade process, there may be many instances where you need to find queries that reference a specific business object. This script is meant to assist you when necessary and is not a script that you are required to run prior to upgrading.

See Finding queries that use a specific business object for details.

Listing all panel definition sources

There may be instances where you need to list panel definitions and the related sources. The ListPanelDefinitionSources.sql script generates one row for every source included in a panel. If a panel is defined with two sources, the script will produce two rows for the panel, with each row corresponding to one of the sources.

Use this script with either an iMIS 2017 or EMS database.

Copy 
-- Get entity sources
WITH XMLNAMESPACES(DEFAULT 'http://schemas.imis.com/2008/01/SharedDataContracts', 'http://schemas.microsoft.com/2003/10/Serialization/Arrays' AS a),
PanelXml AS (
    SELECT [PanelDefinitionId],
           [Name] AS PanelName,
           [Description] AS PanelDescription,
           [RelatedEntityName],
           CAST([SerializedPanelDefinition] AS xml) AS [PanelDefinitionXml]
      FROM [dbo].[PanelDefinition] 
), PanelSources AS (
    SELECT [PanelDefinitionId], [PanelName], [PanelDescription],
           CAST(c.query('./text()') AS sysname) AS Source
      FROM PanelXml x
           CROSS APPLY [PanelDefinitionXml].nodes('/*/SelectedEntityNames/*') AS t(c)
)
SELECT *
FROM PanelSources
Identifying where custom triggers are being used and filtering out the ASI triggers

After running the ListNonImisDatabaseObjectNames script,execute the following script to identify which tables have custom triggers in use. Use this script to filter out ASI-specific triggers, allowing you to focus exclusively on custom tables and their associated triggers.

Copy 
--Identify what tables the triggers are on
SELECT [name] AS TriggerName, OBJECT_NAME(parent_id) AS TableName
  FROM sys.triggers
ORDER BY TableName;

--Filter out the ASI triggers 
SELECT [name] AS TriggerName, OBJECT_NAME(parent_id) AS TableName
  FROM sys.triggers
WHERE [name] NOT LIKE 'asi_%' ESCAPE '\'
   AND [name] != 'Users_Insert_Update_Delete'
ORDER BY TableName;