SQL Queries - CAST Knowledge Base - Queries on objects - Identify and clean up old objects and projects in the knowledge base

This page helps with cleaning up of old projects in the knowledge base. 

These projects may not be identified by the ghost queries so they need to be cleaned separately.

You may see this in Enlighten where both the new project and the old project show up as shown in the screenshot below:

Removing Old Projects

The first step and most important part of the process to remove the old projects is to be able to identify the project in the knowledge base.  This may be challenging if you don't know the prior project name that is still in the KB that you want to delete. 

As shown above, normally there would be in Enlighten both the old project and new project showing up which can help with the identification. With this name, you would then run the following query and identify the row for the older project (in our case the application name starts with APP in both cases):

SELECT *
FROM   keys
WHERE  keynam LIKE '%APP%';

As an example you can find rows like the following data in the results.  The dates can further justify the older project from the newer project.

In this case the 'APP' project is the newer project and the 'APP_8.0.1' project is the older project we need to delete. 

We need the value of the first column (idkey).  In the example above this would be 4021101 for the old project 'APP_8.0.1'.

Once this has been identified do the following to ensure you can go back to this point in case of any improper deletion:

Do the following:  BACKUP THE DATABASES

Then run the following queries to remove this older project from the knowledge base using the idkey value from above (in our example above this is 4021101 - substitute your value in this case for 'xxxxx') - none of these queries return any values of significance as these are mostly DDL queries:

SELECT project_delete(idroot)
FROM ( SELECT idroot
FROM usrproroot
WHERE idusrpro IN (xxxxx)
);

CREATE TEMPORARY TABLE WK_ObjToDelete(IDOBJ INT NOT NULL);
INSERT
INTO WK_ObjToDelete
(
IDOBJ
)
VALUES
(
xxxxx
);

SELECT INT_OBJECT_DEL();

SELECT droptemporarytable(‘WK_ObjToDelete’);

DELETE
FROM usrproroot
WHERE idusrpro IN (xxxxx);

DELETE
FROM   usrpro
WHERE  idusrpro IN (xxxxx);

Here's another approach that can work for you as well.  Please make sure that you have backups of the databases as mentioned above before trying this (all queries are on the KB):

- Find an object which should be deleted using Enlighten (ctrl-shift-d and mouse up will give you ids) - you can confirm this in the keys table(xxx is the id you have found):

SELECT *
FROM   keys
WHERE  idkey=xxx;

The result will be something like this:

15219699;"Object1";"";"XXXXXX";-1;36540;0;138871;"???";"1970-01-01 00:00:00";"";0;""

then find the id of the project associated with this item (again xxx is the id of the object found above):

SELECT distinct idpro
FROM   objpro
WHERE  prop = 0
AND  idobj=xxx;

The result coming back will be id of the project:

38599201

You can confirm this as well in the keys table - normally it will be something like this and what you should also see in Enlighten as the base project (yyy is the project id returned by the query):

SELECT *
FROM   keys
WHERE  idkey=yyy;

Once this is all confirmed, then you delete this project by running the following on the id of the project you have found (yyy as described above):

SELECT PROJECT_DELETE(yyy);