/*
vd20130222 
A generel script for cleaning up rubbish
It's not in any way crucial that this script is run, hence the placement in the ONCE folder
*/

DELETE FROM CustomerAttachmentXTab 
WHERE CustomerNumber NOT IN (SELECT CustomerNumber 
                             FROM Customer WITH (NOLOCK) 
                             WHERE MarkAsDeleted = 0)

DELETE FROM CustomerAttachmentXTab
WHERE 
  AttachmentNumber IN (SELECT AttachmentNumber 
                       FROM Attachment WITH (NOLOCK) 
                       WHERE Batch = 1)

DELETE FROM ReservationAttachmentXTab
WHERE 
  AttachmentNumber IN (SELECT AttachmentNumber 
                       FROM Attachment WITH (NOLOCK) 
                       WHERE Batch = 1)

/*
DELETE FROM ProductCertificate
WHERE 
  FK_CertificateAttachmentID IN (SELECT AttachmentNumber 
                                 FROM Attachment WITH (NOLOCK) 
                                 WHERE Batch = 1)
*/

DELETE FROM Attachment 
WHERE 
  AttachmentNumber NOT IN (SELECT FK_CertificateAttachmentID 
                           FROM ProductCertificate WITH (NOLOCK)) AND 
  AttachmentNumber NOT IN (SELECT AttachmentNumber 
                           FROM CustomerAttachmentXTab WITH (NOLOCK)) AND
  AttachmentNumber NOT IN (SELECT AttachmentNumber
                           FROM ReservationAttachmentXTab WITH (NOLOCK)) AND                           
  AttachmentNumber NOT IN (SELECT FK_CertificateAttachmentID
                           FROM ProductCertificate WITH (NOLOCK))
 
IF EXISTS (SELECT * FROM sys.objects 
           WHERE object_id = OBJECT_ID(N'DeleteInvalidCustomers') AND type in (N'P', N'PC'))
  EXEC DeleteInvalidCustomers    


                          
                           

