A note from 2026: This article was published in 2017. SAP hybris is now SAP Commerce Cloud, and legacy Cockpit/HMC/Product Cockpit tooling has been deprecated in favor of Backoffice and SmartEdit; scripts that query platform tables directly should be validated against your current SAP Commerce Cloud version.

Finding all external references to an object (EARLY BETA)

The script I present today is designed for finding references to any particular item in hybris. It can be useful if you want to figure out whether a product or image is used somewhere in the system or whether it can be safely removed. It is a beta version, and it doesn’t cover everything, so use it at your own risk 🙂

For demonstration purposes, let’s take an image that is definitely linked to the product.

Let’s start with the simplest case:

Product media reference example

The script shows all objects (here, products) that have the particular object (here, Media) set as a value of one of the attributes with the “Media” type. For 8797134192670, the script shows:

Script output showing product references

The first item in the list is a product with the PK 8796373942273. All other items are product variants that have the image assigned as well.

There is a special case with collections of objects. A collection is stored as comma-separated PKs. In SAP hybris accelerators, collections are used quite intensively, despite the fact that SAP doesn’t recommend using them in projects. Let’s take the following example:

Product media container media reference

Product media container details

We expect the system to determine the objects this image is used with, and one of these is this product.

String pkObj = "8797135929374";

The script shows all objects that have references to the object with this PK:

Script output showing references to the media object

We can see that our expected product is the first item in the list. All other products are different versions of the product.

This media is found in the collection attribute, “thumbnail”. Internally, collections are stored as comma-separated values, so for our product the value of the attribute looks like this:

,#1,8797135896606,8797135962142,8797135994910, **8797135929374**,8797136027678,
8797136060446, 8797136093214,

Let’s take another example:

Point of service media container example

For the particular media container (8796244312114), the system shows the list of objects that use the media container.

Script output showing media container references

You can see both Media objects on the list because both use the same media container.

Categories

Let’s take the following example:

Category example in SAP Commerce

For PK=8796146172046 (Digital Compacts, the Category object), the script shows the following results:

Script output showing category references

That means that the category is used in the following external objects:

The PKs specified are the PKs of these relations (for example, CategoryProductRelation), not the related objects (Product).

For the Brands category (8796147253390), you will get a report like the following:

Script output for the Brands category

There are no products because the Brand category is not linked to products directly.

How it works

The system determines the type of the specified PK. For this type, the system extracts the type hierarchy. For each type in the hierarchy, the system finds the types with attributes of the particular types or types involved in hybris relations. For collections and relations, there is special handling. Finally, the system creates an SQL query and executes it. For example, for the “Category” object, the SQL will look like this:

SELECT 'ClassAttributeAssignment','cat2attrrel',"(cat2attrrel.p_classificationclass = '8796146172046' AND TypePkString = 8796110258258)", cat2attrrel.pk
FROM cat2attrrel
WHERE
(cat2attrrel.p_classificationclass = '8796146172046' AND TypePkString = 8796110258258)
UNION
SELECT 'CockpitItemTemplate','cockpittemplclassifrels',"(cockpittemplclassifrels.TargetPK = '8796146172046')", cockpittemplclassifrels.pk
FROM cockpittemplclassifrels
WHERE
(cockpittemplclassifrels.TargetPK = '8796146172046')
UNION
SELECT 'TextFieldConfiguratorSetting','configuratorsettings',"(configuratorsettings.p_configurationcategory = '8796146172046' AND TypePkString = 8796109111378)", configuratorsettings.pk
FROM configuratorsettings
WHERE
(configuratorsettings.p_configurationcategory = '8796146172046' AND TypePkString = 8796109111378)
UNION
SELECT 'CategoryCatalogVersionDifference','catverdiffs',"(catverdiffs.p_sourcecategory = '8796146172046' AND TypePkString = 8796110061650) OR (catverdiffs.p_targetcategory = '8796146172046' AND TypePkString = 8796110061650)", catverdiffs.pk
FROM catverdiffs
WHERE
(catverdiffs.p_sourcecategory = '8796146172046' AND TypePkString = 8796110061650) OR
(catverdiffs.p_targetcategory = '8796146172046' AND TypePkString = 8796110061650)
UNION
SELECT 'CMSSite','cmssite',"(cmssite.p_defaultpreviewcategory = '8796146172046' AND TypePkString = 8796105179218)", cmssite.pk
FROM cmssite
WHERE
(cmssite.p_defaultpreviewcategory = '8796146172046' AND TypePkString = 8796105179218)
UNION
SELECT 'SolrCategoryRedirect','solrabskwrdredir',"(solrabskwrdredir.p_redirectitem = '8796146172046' AND TypePkString = 8796119203922)", solrabskwrdredir.pk
FROM solrabskwrdredir
WHERE
(solrabskwrdredir.p_redirectitem = '8796146172046' AND TypePkString = 8796119203922)
UNION
SELECT 'PreviewData','previewdata',"(previewdata.p_previewcategory = '8796146172046' AND TypePkString = 8796126675026)", previewdata.pk
FROM previewdata
WHERE
(previewdata.p_previewcategory = '8796146172046' AND TypePkString = 8796126675026)
UNION
SELECT 'AsCategoryAwareSearchConfiguration','assearchconf',"(assearchconf.p_category = '8796146172046' AND TypePkString = 8796128575570)", assearchconf.pk
FROM assearchconf
WHERE
(assearchconf.p_category = '8796146172046' AND TypePkString = 8796128575570)
UNION
SELECT 'ProductCategoryRestriction','categories',"(restrictions.p_categories LIKE '%8796146172046%')", restrictions.pk
FROM restrictions
WHERE
(restrictions.p_categories LIKE '%8796146172046%')
UNION
SELECT 'Principal','cat2princrel',"(cat2princrel.SourcePK = '8796146172046')", cat2princrel.pk
FROM cat2princrel
WHERE
(cat2princrel.SourcePK = '8796146172046')
UNION
SELECT 'Media','cat2medrel',"(cat2medrel.SourcePK = '8796146172046')", cat2medrel.pk
FROM cat2medrel
WHERE
(cat2medrel.SourcePK = '8796146172046')
UNION
SELECT 'Product','cat2prodrel',"(cat2prodrel.SourcePK = '8796146172046')", cat2prodrel.pk
FROM cat2prodrel
WHERE
(cat2prodrel.SourcePK = '8796146172046')
UNION
SELECT 'Keyword','cat2keywordrel',"(cat2keywordrel.SourcePK = '8796146172046')", cat2keywordrel.pk
FROM cat2keywordrel
WHERE
(cat2keywordrel.SourcePK = '8796146172046')
UNION
SELECT 'Category','cat2catrel',"(cat2catrel.SourcePK = '8796146172046') OR (cat2catrel.TargetPK = '8796146172046')", cat2catrel.pk
FROM cat2catrel
WHERE
(cat2catrel.SourcePK = '8796146172046') OR
(cat2catrel.TargetPK = '8796146172046')
UNION
SELECT 'ProductPromotion','catpromrelations',"(catpromrelations.SourcePK = '8796146172046')", catpromrelations.pk
FROM catpromrelations
WHERE
(catpromrelations.SourcePK = '8796146172046')
UNION
SELECT 'CMSCategoryRestriction','categories4restriction',"(categories4restriction.TargetPK = '8796146172046')", categories4restriction.pk
FROM categories4restriction
WHERE
(categories4restriction.TargetPK = '8796146172046')
UNION
SELECT 'ProductCarouselComponent','catsforprodcarcomprels',"(catsforprodcarcomprels.TargetPK = '8796146172046')", catsforprodcarcomprels.pk
FROM catsforprodcarcomprels
WHERE
(catsforprodcarcomprels.TargetPK = '8796146172046')

The script is in BETA.
Some cases are not covered (such as localized composed type attributes).
Please use it at your own risk: the script code is quick and dirty, and it was developed mainly for experimentation.

THE SCRIPT

(github)

© Rauf Aliev, December 2017