KB438365: How to manually purge information from the Enterprise Manager warehouse lookup tables in MicroStrategy 10.x

Quality Engineer, Senior • MicroStrategy

In some scenarios, it may be desirable to manually purge Enterprise Manager lookup table data from the warehouse database. This KB article will provide SQL queries to do so.

Important Note:
By design, the SQL queries provided in this KB article will just result in the deletion of data. It is recommended to make a backup of the warehouse before proceeding to safeguard against accidental data loss.
The knowledge base article KB330665: How to manually purge Statistics and Enterprise Manager warehouse data in MicroStrategy 10.x details queries that may be used to manually purge statistics and fact data from the Statistics and Enterprise Manager warehouse. If the desired outcome is to completely remove all references to particular projects or specific Metadata repositories from the warehouse, the lookup data (i.e., information about project metadata objects) should also be purged. Lookup data must not be purged before purging statistics or fact data as this would cause Enterprise Manager reports to return incomplete or incorrect results.
In order to purge lookup data, the corresponding Metadata repository GUID and project GUID (if the data is related to a project) must be known. The project GUID can be easily determined via Strategy Developer for projects that still exist, or by examining the IS_PROJ table in the Enterprise Manager warehouse for projects that do not still exist in the Metadata. The Metadata repository GUID will need to be manually determined by the Strategy administrator. The following query may be helpful as it will list the Server Definition names with their corresponding Metadata repository ID and GUIDs:


SELECT * from IS_SERVER JOIN EM_MD on IS_REPOSITORY_ID = REP_ID;

Once the Metadata repository GUID(s) and project GUID(s) are known, purge the lookup data using the following queries, replacing the text <PROJ_GUID> with the GUID of the Strategy Project to purge data for, and <REP_GUID> with the corresponding Metadata repository GUID. Multiple project or repository GUIDs can be used, separated by commas. The queries below target lookup tables as well as relate tables present in the Strategy Enterprise Manager Warehouse.


DELETE FROM IS_ATT_ATT_FORM WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_ATT_HIER WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_COL_TABLE WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_MET_TEMP WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_REP_ATT WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_REP_CONS WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_REP_DOC WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_REP_FILT WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_REP_MET WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_REP_HIER WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_REP_PROMPT WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_REP_REP WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_REP_TEMPLATE WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_SCHED_REL_DOC WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_SCHED_RELATE WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_TABLE_FACT WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_TEMP_ATT WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_USER_PROJ_SF WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_SF_ATT WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_ATT WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_ATT_FORM WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_COL WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_CONS WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_CUST_GP WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_DOC WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_DB_TAB WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_FACT WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_FILT WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_HIER WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_MET WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_PROMPT WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_REP WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_SEC_FILT WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_TABLE WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_TEMP WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_TRANS WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_PROJ WHERE IS_PROJ_GUID IN ('<PROJ_GUID>') AND IS_REPOSITORY_ID IN (SELECT DISTINCT REP_ID FROM EM_MD WHERE REP_GUID IN ('<REP_GUID>'));

For lookup tables corresponding to Configuration Objects, data is not Project specific so only the Metadata repository GUID is required:


DELETE FROM IS_DEVICE WHERE IS_REPOSITORY_ID IN (SELECT DISTINCT REP_ID FROM EM_MD WHERE REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_EVENT WHERE IS_REPOSITORY_ID IN (SELECT DISTINCT REP_ID FROM EM_MD WHERE REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_SCHED WHERE IS_REPOSITORY_ID IN (SELECT DISTINCT REP_ID FROM EM_MD WHERE REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_TRANSMIT WHERE IS_REPOSITORY_ID IN (SELECT DISTINCT REP_ID FROM EM_MD WHERE REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_DB_INST WHERE IS_REPOSITORY_ID IN (SELECT DISTINCT REP_ID FROM EM_MD WHERE REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_SERVER WHERE IS_REPOSITORY_ID IN (SELECT DISTINCT REP_ID FROM EM_MD WHERE REP_GUID IN ('<REP_GUID>'));
DELETE FROM EM_USER WHERE IS_REPOSITORY_ID IN (SELECT DISTINCT REP_ID FROM EM_MD WHERE REP_GUID IN ('<REP_GUID>'));
DELETE FROM EM_USR_GP WHERE IS_REPOSITORY_ID IN (SELECT DISTINCT REP_ID FROM EM_MD WHERE REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_DB_INST1 WHERE IS_REPOSITORY_ID IN (SELECT DISTINCT REP_ID FROM EM_MD WHERE REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_USR_GP_USR_GP WHERE IS_REPOSITORY_ID IN (SELECT DISTINCT REP_ID FROM EM_MD WHERE REP_GUID IN ('<REP_GUID>'));
DELETE FROM IS_USR_GP_USER WHERE IS_REPOSITORY_ID IN (SELECT DISTINCT REP_ID FROM EM_MD WHERE REP_GUID IN ('<REP_GUID>'));

KB438365

Comment

0 comments

Details

Knowledge Article

Published:

July 20, 2017

Last Updated:

January 4, 2019

DELETE FROM IS_ATT_ATT_FORM WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_ATT_HIER WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_COL_TABLE WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_MET_TEMP WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_REP_ATT WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_REP_CONS WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_REP_DOC WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_REP_FILT WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_REP_MET WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_REP_HIER WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_REP_PROMPT WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_REP_REP WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_REP_TEMPLATE WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_SCHED_REL_DOC WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_SCHED_RELATE WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_TABLE_FACT WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_TEMP_ATT WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_USER_PROJ_SF WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_SF_ATT WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_ATT WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_ATT_FORM WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_COL WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_CONS WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_CUST_GP WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_DOC WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_DB_TAB WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_FACT WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_FILT WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_HIER WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_MET WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_PROMPT WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_REP WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_SEC_FILT WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_TABLE WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_TEMP WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_TRANS WHERE IS_PROJ_ID IN (SELECT DISTINCT IS_PROJ_ID FROM IS_PROJ , EM_MD WHERE IS_PROJ_GUID IN('<PROJ_GUID>') AND IS_REPOSITORY_ID=REP_ID AND REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_PROJ WHERE IS_PROJ_GUID IN ('<PROJ_GUID>') AND IS_REPOSITORY_ID IN (SELECT DISTINCT REP_ID FROM EM_MD WHERE REP_GUID IN ('<REP_GUID>'));

DELETE FROM IS_DEVICE WHERE IS_REPOSITORY_ID IN (SELECT DISTINCT REP_ID FROM EM_MD WHERE REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_EVENT WHERE IS_REPOSITORY_ID IN (SELECT DISTINCT REP_ID FROM EM_MD WHERE REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_SCHED WHERE IS_REPOSITORY_ID IN (SELECT DISTINCT REP_ID FROM EM_MD WHERE REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_TRANSMIT WHERE IS_REPOSITORY_ID IN (SELECT DISTINCT REP_ID FROM EM_MD WHERE REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_DB_INST WHERE IS_REPOSITORY_ID IN (SELECT DISTINCT REP_ID FROM EM_MD WHERE REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_SERVER WHERE IS_REPOSITORY_ID IN (SELECT DISTINCT REP_ID FROM EM_MD WHERE REP_GUID IN ('<REP_GUID>')); DELETE FROM EM_USER WHERE IS_REPOSITORY_ID IN (SELECT DISTINCT REP_ID FROM EM_MD WHERE REP_GUID IN ('<REP_GUID>')); DELETE FROM EM_USR_GP WHERE IS_REPOSITORY_ID IN (SELECT DISTINCT REP_ID FROM EM_MD WHERE REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_DB_INST1 WHERE IS_REPOSITORY_ID IN (SELECT DISTINCT REP_ID FROM EM_MD WHERE REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_USR_GP_USR_GP WHERE IS_REPOSITORY_ID IN (SELECT DISTINCT REP_ID FROM EM_MD WHERE REP_GUID IN ('<REP_GUID>')); DELETE FROM IS_USR_GP_USER WHERE IS_REPOSITORY_ID IN (SELECT DISTINCT REP_ID FROM EM_MD WHERE REP_GUID IN ('<REP_GUID>'));