/* ClearCompanys.sql - Script that will clear out all entrys in the DYNAMICS
database referencing databases that no longer exist on the SQL Server.
 
Requirements:
Company database you wish to have cleaned out of the tables in the DYNAMICS
database must be removed from the SQL server before running this script.
Ensure that all your databases have been restored or they will be erased
from the DYNAMICS database.
*/
 
SET nocount ON
 
/* Remove all references in the company master (SY01500) for databases that
Do not exist on the SQL Server */
DELETE  DYNAMICS..SY01500
WHERE   INTERID NOT IN ( SELECT name
                         FROM   master..sysdatabases )
 
/* Clear out all tables in DYNAMICS database that have a CMPANYID field
that no longer matches any Company ID's in the SY01500 */
USE DYNAMICS
DECLARE @CMPANYID CHAR(150)
DECLARE CMPANYID_Cleanup CURSOR
    FOR SELECT  'delete ' + o.name + ' where CMPANYID not in (0,-32767)'
                + ' and CMPANYID not in (select CMPANYID from DYNAMICS..SY01500)'
        FROM    sysobjects o,
                syscolumns c
        WHERE   o.id = c.id
                AND o.TYPE = 'U'
                AND c.name = 'CMPANYID'
                AND o.name <> 'SY01500'
        ORDER BY o.name
 
OPEN CMPANYID_Cleanup
FETCH NEXT FROM CMPANYID_Cleanup INTO @CMPANYID
 
WHILE ( @@FETCH_STATUS <> -1 )
    BEGIN
 
        EXEC ( @CMPANYID
            )
        FETCH NEXT FROM CMPANYID_Cleanup INTO @CMPANYID
 
    END
 
DEALLOCATE CMPANYID_Cleanup
GO
 
/* Clear out all tables in DYNAMICS database that have a companyID field
that no longer matches any Company ID's in the SY01500 */
USE DYNAMICS
DECLARE @companyID CHAR(150)
DECLARE companyID_Cleanup CURSOR
    FOR SELECT  'delete ' + o.name + ' where companyID not in (0,-32767)'
                + ' and companyID not in (select CMPANYID from DYNAMICS..SY01500)'
        FROM    sysobjects o,
                syscolumns c
        WHERE   o.id = c.id
                AND o.TYPE = 'U'
                AND c.name = 'companyID'
                AND o.name <> 'SY01500'
SET nocount ON
OPEN companyID_Cleanup
FETCH NEXT FROM companyID_Cleanup INTO @companyID
WHILE ( @@FETCH_STATUS <> -1 )
    BEGIN
 
        EXEC ( @companyID
            )
        FETCH NEXT FROM companyID_Cleanup INTO @companyID
 
    END
 
DEALLOCATE companyID_Cleanup
GO
 
/* Clear out all tables in DYNAMICS database that have a db_name field
that no longer matches any company names (INTERID) in the SY01500 */
USE DYNAMICS
DECLARE @db_name CHAR(150)
DECLARE db_name_Cleanup CURSOR
    FOR SELECT  'delete ' + o.name
                + ' where db_name <> ''DYNAMICS'' and db_name <> ''''
and db_name not in (select INTERID from DYNAMICS..SY01500)'
        FROM    sysobjects o,
                syscolumns c
        WHERE   o.id = c.id
                AND o.TYPE = 'U'
                AND c.name = 'db_name'
 
SET nocount ON
OPEN db_name_Cleanup
FETCH NEXT FROM db_name_Cleanup INTO @db_name
 
WHILE ( @@FETCH_STATUS <> -1 )
    BEGIN
 
        EXEC ( @db_name
            )
        FETCH NEXT FROM db_name_Cleanup INTO @db_name
 
    END
 
DEALLOCATE db_name_Cleanup
GO
SET nocount ON
 
/* Clear out all tables in DYNAMICS database that have a dbname field
that no longer matches any company names (INTERID) in the SY01500 */
USE DYNAMICS
DECLARE @dbname CHAR(150)
DECLARE dbname_Cleanup CURSOR
    FOR SELECT  'delete ' + o.name
                + ' where DBNAME <> ''DYNAMICS'' and DBNAME <> ''''
and DBNAME not in (select INTERID from DYNAMICS..SY01500)'
        FROM    sysobjects o,
                syscolumns c
        WHERE   o.id = c.id
                AND o.TYPE = 'U'
                AND c.name = 'DBNAME'
 
SET nocount ON
OPEN dbname_Cleanup
FETCH NEXT FROM dbname_Cleanup INTO @dbname
 
WHILE ( @@FETCH_STATUS <> -1 )
    BEGIN
 
        EXEC ( @dbname
            )
        FETCH NEXT FROM dbname_Cleanup INTO @dbname
 
    END
 
DEALLOCATE dbname_Cleanup
GO
SET nocount ON
 
/* Remove all stranded references from the other Business Alerts table that
no longer exist in the SY40500 */
DELETE  SY40502
WHERE   BARULEID NOT IN ( SELECT    BARULEID
                          FROM      SY40500 )
DELETE  SY40503
WHERE   BARULEID NOT IN ( SELECT    BARULEID
                          FROM      SY40500 )
DELETE  SY40504
WHERE   BARULEID NOT IN ( SELECT    BARULEID
                          FROM      SY40500 )
DELETE  SY40505
WHERE   BARULEID NOT IN ( SELECT    BARULEID
                          FROM      SY40500 )
DELETE  SY40506
WHERE   BARULEID NOT IN ( SELECT    BARULEID
                          FROM      SY40500 )
GO