How to remove Custom Discovery Information from the Database

I was working with a customer recently on an issue where they had added custom discovery information but they needed to change some of it.  In doing some research I found that there is not a lot of information on how to remove the customer discovery information.

A word of caution before proceeding.  This is totally unsupported by Microsoft and you are on your own.   Please make sure that you have a good backup of the Database before trying any of the steps below.

1. Stop the SMS_Executive and SMS_Site_Component Services on the site with the Database that has the issue.

2.  Delete the entry out of the wbem repository.  Use wbemtest and connect to root\sms\site_sitecode.  Once connected click on the Enum Classes button and then find the SMS_R_STRMetrics class and highlight like this screen shot:

Next hit the Delete button and that should remove it.  The item above is the name of your custom discovery class.

3.  Verify the name of the table that the custom architecture is in.  For me it is called Cust_Arch_7_Disc.  Once you have the table name then you need to drop the table using this SQL command:  Drop table Cust_Arch_7_Disc

4. Next we need to do the same thing with the view.  Mine was called v_R_Cust_Arch_7.  Once you get that view name then drop it using this SQL command:  Drop view v_R_Cust_Arch_7

5.  In my tables I had a reference to a table and it would not let me delete the entries related to the Custom Architecture until I cleaned that out.  Here is more information.  I was trying to run this SQL command:  delete from DiscPropertyDefs where DiscArchKey = '7'.  When I run this command I get this error: 

Msg 99951, Level 16, State 1, Procedure DiscPropertyDefs_del, Line 1

Cannot delete row because referenced table exists in database.

Msg 3609, Level 16, State 1, Line 1

The transaction ended in the trigger. The batch has been aborted.

If you run a Select * from DiscPropertyDefs where DiscArchKey = '7' then you will see this in the results:

It is those entries in the ArrayTableName column that is causing the issue.  Run this SQL command to fix that:  update DiscPropertyDefs set ArrayTableName = '' where DiscArchKey = '7' Once this complete do another Select * from DiscPropertyDefs where DiscArchKey = '7' to verify that all rows in the ArrayTableName column are blank.  Once that is the case then you are ready for the next step.

6.  Run this SQL query to clean up the DiscPropertyDefs table:  delete from DiscPropertyDefs where ArrayTableName = '' and DiscArchKey = '7’

7. Run this SQL query to clean up the DiscoveryArchitectures table:  delete from DiscoveryArchitectures where BaseTableName='CUST_ARCH_7_DISC'

8. Run this SQL query to clean up the DiscItemAgents table:  delete from DiscItemAgents where DiscArchKey = '7’.  You may get a 0 rows affected on this statement as there is a trigger when a delete statement is issued on the DiscoveryArchitectures table that will delete entries in this table as well.  Go ahead and run the delete statement on this table just in case.

9. Restart the SMS Services on the box

10. In the console delete any collections and queries that referenced this custom architecture.

That should clear up the custom discovery that was added.  You will need to perform this on all primary sites where the information was added.