# SQL for cleaning orphan metadata resources

**URL:** <https://community.dhis2.org/t/sql-for-cleaning-orphan-metadata-resources/59591>\
**Category:** Implementation - Implémentation\
**Created:** [18 June 2024 12:21 UTC](https://community.dhis2.org/t/sql-for-cleaning-orphan-metadata-resources/59591 "2024-06-18T12:21:16Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![ctejo](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/ctejo/32/42385_2.png) [@ctejo](https://community.dhis2.org/u/ctejo)\
**Post date:** [18 June 2024 12:21 UTC](https://community.dhis2.org/t/sql-for-cleaning-orphan-metadata-resources/59591/1 "2024-06-18T12:21:16Z")

</div>

We (in @solidlines ) are facing the issue of “orphan metadata” (metadata resources that should be linked to another “main” resources, but there are not) in dhis2 instances that are running for a long time.

**Do you have another examples to share ?**

I would like to compile the different issues that are arising and an approach for cleaning them from the system using SQL.

**1. Program Rule Action (PRA) without Program Rule**

For instance, a Program Rule Action (PRA) must be linked to a Program Rule (actually, you cannot edit a PRA using the UI). But there are cases were this link is broken.

`SELECT * FROM programruleaction WHERE programruleid IS NULL`

Or using the API REST:

`/api/programRuleActions?filter=programRule:null`

In order to remove them, you can go to the db and execute:

`DELETE FROM programruleaction WHERE programruleid IS NULL;`

**2. Option without OptionSet**

Another case is Options without a link to a OptionSet. You can know them executing:

`SELECT * FROM optionvalue WHERE optionsetid IS NULL;`

Or using the API REST:

`/api/options?filter=optionSet:null`

Sometimes, the option is used in other tables, so prior to remove it, you need to clean the others executing these SQL in order:

```auto
SELECT * FROM programruleaction WHERE optionid IN (SELECT optionid FROM optionvalue WHERE optionsetid IS NULL);
UPDATE programruleaction SET optionid = NULL WHERE optionid IN (SELECT optionid FROM optionvalue WHERE optionsetid IS NULL);

```

After that, and in order to remove them, you can go to the db and execute:  
`DELETE FROM optionvalue WHERE optionsetid IS NULL;`

**3. Program Stage Section without Program Stage**

Another case is Program Stage Sections without a link to a Program Stage. You can know them executing:

`SELECT * FROM programstagesection WHERE programstageid IS NULL;`

Or using the API REST:

`/api/programStageSections?filter=programStage:null`

Sometimes, the program stage section is used in other tables, so prior to remove it, you need to clean the others executing these SQL in order:

```auto
UPDATE programruleaction SET programstagesectionid = NULL WHERE programstagesectionid IN (SELECT programstagesectionid FROM programstagesection WHERE programstageid IS NULL);
DELETE FROM programstagesection_programindicators WHERE programstagesectionid IN (SELECT programstagesectionid FROM programstagesection WHERE programstageid IS NULL);
DELETE FROM programstagesection_dataelements WHERE programstagesectionid IN (SELECT programstagesectionid FROM programstagesection WHERE programstageid IS NULL);

```

After that, and in order to remove them, you can go to the db and execute:  
`DELETE FROM programstagesection WHERE programstageid IS NULL;`

**4. Program Stage without Program**

Another case is Program Stage without a link to a Program. You can know them executing:

`SELECT * from programstage WHERE programid IS NULL;`

Or using the API REST:

`/api/programStages?filter=program:null`

Sometimes, the program stage is used in other tables, so prior to remove it, you need to clean the others, executing these SQL in order:

```auto
SELECT * FROM programstagedataelement WHERE programstageid IN (SELECT programstageid from programstage WHERE programid IS NULL);
DELETE FROM programstagedataelement WHERE programstageid IN (SELECT programstageid from programstage WHERE programid IS NULL);

```

After that, and in order to remove them, you can go to the db and execute:

`DELETE FROM programstage WHERE programid IS NULL;`

---

<div class="post-metadata">

**Author:** ![jason](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/jason/32/32739_2.png) [@jason](https://community.dhis2.org/u/jason)\
**Post date:** [18 June 2024 12:46 UTC](https://community.dhis2.org/t/sql-for-cleaning-orphan-metadata-resources/59591/2 "2024-06-18T12:46:16Z")

</div>

Hi @ctejo  
Thanks for your post. We have added a number of different integrity checks in version 41 of DHIS2. I will have a look and see if we have included these, as they seem to be useful.

The new metadata integrity feature is documented [here](https://docs.dhis2.org/en/develop/using-the-api/dhis-core-version-241/data-validation.html?h=metadata+integrity#webapi_data_integrity_run_summary)

Best regards,  
Jason

---

<div class="post-metadata">

**Author:** ![ctejo](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/ctejo/32/42385_2.png) [@ctejo](https://community.dhis2.org/u/ctejo)\
**Post date:** [18 June 2024 13:00 UTC](https://community.dhis2.org/t/sql-for-cleaning-orphan-metadata-resources/59591/3 "2024-06-18T13:00:56Z")

</div>

Thanks Jason. Happy to see that they might be useful for the community!

---

<div class="post-metadata">

**Author:** ![ctejo](https://dhis2.b-cdn.net/user_avatar/community.dhis2.org/ctejo/32/42385_2.png) [@ctejo](https://community.dhis2.org/u/ctejo)\
**Post date:** [24 June 2024 07:52 UTC](https://community.dhis2.org/t/sql-for-cleaning-orphan-metadata-resources/59591/4 "2024-06-24T07:52:36Z")

</div>

Just updated the post including the API REST calls that could help
