print 'delete from account'
delete from account 
 where createdate >= '2005-09-1'
go

print 'delete from accountsummary'
delete from accountsummary 
 where not exists (select * from account where accountid = accountsummary.accountid)
go

print 'delete from c_account'
delete from c_account 
 where not exists (select * from account where accountid = c_account.accountid)
go

print 'delete from c_profile'
delete from c_profile
 where not exists (select * from account where accountid = c_profile.accountid)
go

print 'delete from contact'
delete from contact 
 where not exists (select * from account where accountid = contact.accountid)
go

print 'delete from c_contact'
delete from c_contact
 where not exists (select * from contact where contactid = c_contact.contactid)
go

print 'delete from c_contact_profile'
delete from c_contact_profile
 where not exists (select * from contact where contactid = c_contact_profile.contactid)
go

print 'delete account addresses'
delete from address 
 where entityid like 'a%' 
   and entityid not in ('ADMIN') 
   and not exists (select * from account where accountid = address.entityid)
go

print 'delete contact addresses'
delete from address 
 where entityid like 'c%'
   and not exists (select * from contact where contactid = address.entityid)
go

print 'detete from c_transactions'
delete from c_transactions 
 where not exists (select * from account where accountid = c_transactions.accountid)
go

print 'detete from history'
delete from history 
 where (Accountid is not null and not exists (select * from account where accountid = history.accountid))
 or (Contactid is not null and not exists (select * from contact where contactid = history.contactid))
go

print 'detete from activity'
delete from activity
 where (Accountid is not null and not exists (select * from account where accountid = activity.accountid))
 or (Contactid is not null and not exists (select * from contact where contactid = activity.contactid))
go

Sub btnGrabTableInfoClick(Sender)
    Dim theCon, strSQL, theRS, theRS2, i, x, strPreviousNewStatus, iNextRow
    Dim xlApp, xlWb, xlWs, strAppVersion
    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Add
    Set xlWs = xlWb.Worksheets("Sheet1")
    strAppVersion = Mid(xlApp.Version, 1, InStr(1, xlApp.Version, ".") - 1)
    xlApp.Visible = True
    xlApp.UserControl = True
    iNextRow = 5

    If CDbl(strAppVersion) > 8 Then
        'Insert Title to Worksheet
        xlWs.Range("A1:Z4").Font.Bold = True
        xlWs.Cells(1, 1).Value = "List of Table Names in User and the Number of Records"
        xlWs.Cells(2, 1).Value = "Printed: " & FormatDateTime(Now, 2)

        ' Set the column names using the Caption text from the grid
        xlWs.Cells(4, 1).Value = "Table Name"
        xlWs.Cells(4, 2).Value = "Number of Records"

        Set theCon = Application.GetNewConnection
        strSQL = "SELECT DISTINCT Table_Name FROM Information_Schema.Columns WHERE Table_Schema = 'sysdba'"
        Set theRS = theCon.Execute(strSQL)
        ProgressBar.Max = theRS.RecordCount

        For i = 1 To theRS.RecordCount
            If Not Right(theRS("Table_Name"), 1) = "_" And theRS("Table_Name") = UCase(theRS("Table_Name")) Then
               xlWs.Cells(iNextRow, 3).Value = theRS("Table_Name")
               strSQL = "SELECT Count(*) AS NumberOfRecords FROM " & theRS("Table_Name")
               Set theRS2 = theCon.Execute(strSQL)
               If theRS2.RecordCount > 0 Then
                  If theRS2("NumberOfRecords") > 0 Then
                      xlWs.Cells(iNextRow, 1).Value = theRS("Table_Name")
                      xlWs.Cells(iNextRow, 2).Value = FormatNumber(theRS2("NumberOfRecords"), 0)
                      xlWs.Cells(iNextRow, 3).Value = ""
                      iNextRow = iNextRow + 1
                  End If
                  xlWs.Cells(iNextRow, 3).Value = ""
               End If
               theRS2.Close
               Set theRS2 = Nothing
               xlWs.Cells(iNextRow, 3).Value = ""
            End If
            theRS.MoveNext
            lblRecordCount.Caption = "Record " & (i) & " of " & theRS.RecordCount
            ProgressBar.StepIt
            Application.BasicFunctions.ProcessWindowMessages
        Next

        xlWs.Range("A4:D300").Columns.AutoFit
    Else
        MsgBox "MS Excel 2000 or newer needs to be installed to export this list.", vbOKOnly, "Export to Excel"
    End If

    Set xlWs = Nothing
    Set xlWb = Nothing
    Set xlApp = Nothing

    theRS.Close
    Set theRS = Nothing
    theCon.Close
    Set theCon = Nothing

    Msgbox "Finished!", 0, "SalesLogix"
End Sub