mes.spPopulateAssetHierarchy

Description

Recomputes and populates the `mes.assetHierarchy_MAT` table by recursively traversing the parent-child relationships of assets. This procedure ensures asset hierarchy and metadata such as types, groups, and performance thresholds are rebuilt for efficient querying and display.

Key Operations

- Clears existing data from `mes.assetHierarchy_MAT` - Recursively assembles the full asset path via a Common Table Expression (CTE) - Joins in additional metadata: asset type, group info, timezone, and performance thresholds - Inserts the enriched structure into the materialized hierarchy table

Usage Notes

This stored procedure should be executed after any structural updates to the asset table (e.g., new assets, reparenting, type changes) to keep the hierarchy current.

Example

-- Refresh the full asset hierarchy
EXEC mes.spPopulateAssetHierarchy;