Dropping Tables for Deleted Record Definitions


While application designer makes it easy to delete record definitions, it doesn't drop the table or view in the database after the record definition has been deleted. So you can be left with orphaned tables or views that have no application designer record definition.

select
    RDEL.RECNAME,
    RDEL.VERSION,
    nvl(AT.TABLE_NAME, AV.VIEW_NAME) as TABLE_VIEW_NAME
from 
    PSRECDEL RDEL left outer join ALL_TABLES AT
    on RDEL.RECNAME = replace(AT.TABLE_NAME, 'PS_', '')
    left outer join ALL_VIEWS AV
    on RDEL.RECNAME = replace(AV.VIEW_NAME, 'PS_', '')    
where
    nvl(AT.TABLE_NAME, AV.VIEW_NAME) != ' '
;

The PSRECDEL table stores record definitions that have been deleted through application designer.

If you want, you can use the following query to automatically generate drop table and drop view statements for any orphaned tables or views that are found:

select    
    (case when AT.TABLE_NAME is null 
     then 'drop view ' || AV.VIEW_NAME || ';'
     else 'drop table ' || AT.TABLE_NAME || ';'
     end) as DROP_STATEMENT
from 
    PSRECDEL RDEL left outer join ALL_TABLES AT
    on RDEL.RECNAME = replace(AT.TABLE_NAME, 'PS_', '')
    left outer join ALL_VIEWS AV
    on RDEL.RECNAME = replace(AV.VIEW_NAME, 'PS_', '')    
where
    nvl(AT.TABLE_NAME, AV.VIEW_NAME) != ' '
;
NOTE: this SQL was written and tested on an Oracle database though it could be easily adapted to SQL server if required.

Revision #1
Created Mon, Jul 29, 2019 10:55 PM by PeopleSoft Wiki
Updated Mon, Jul 29, 2019 10:57 PM by PeopleSoft Wiki