Toad World® Forums

How to generate a delete script to empty all tables of a model?

Hi,

is there a script example available to generate a sql script that deletes all table contents?
Ordering is important to prevent foreign key constraint violations.

I’m using Version 3.5.10.

Kind regards
Andreas

Hi Andreas,

It's quite an interesting question, though we/TDM don't deal with databases content, data, but with structure.
Anyway, to avoid these situations in a new database structures, use CASCADE option as in the screenshot.

And concerning you problem with deleting tables content:
http://stackoverflow.com/questions/868620/sql-script-to-alter-all-foreign-keys-to-add-on-delete-cascade
or
http://www.codeproject.com/KB/database/GenDeleteSQL.aspx

Regards,
Lukas

Thanx, these are possible solutions.

But has no one done this with TDM JScript before?
Shouldn’t be that difficult!?

Regards,
Andreas

Probably another possible solution is to drop database and create is again, incl. sequences/autoincrement settings etc. :slight_smile:

BTW: here is a sample script that iterates entities in the order SQL code is generated:

function main(){
var GlobalListOrder = Model.DefaultCodeGenerator.CreateGlobalOrderList();

var i, obj;
for(i=0;i<GlobalListOrder.Count;i++)
{
obj = GlobalListOrder.GetObject(i);
if(obj.ObjectType == 2002) // 2002 = Entity

    Log.Information(obj.Name+" + custom truncate command");

}
}

Regards,

Vaclav, Petr & TDM team

try this one (Java)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashSet;
import java.util.List;

/**

  • Se as tabelas do banco não tiverem ciclos, é bem possível que este programa
  • gere uma sequencia de deletes na ordem certa das dependências de FK->PK.
  • depende obviamente do driver oracle no classpath

  • chupinhado de http://www.codeproject.com/Articles/160998/A-Small-Utility-to-Generate-Cascading-Delete-SQL-S

  • Maio 2012

  • @author leonardo.kenji

*/
public class DeleteAll {

private static final String PEGAR_FK = "Select C.TABLE_NAME, " +
        "C.COLUMN_NAME as fk from user_cons_columns c " +
        "where c.CONSTRAINT_NAME IN " +
        "(Select constraint_name from (user_constraints) " +
        "where constraint_type='R' and r_constraint_name in " +
        "(select constraint_name from user_constraints " +
        "where constraint_type in ('P','U') and table_name=?))";

/*
 * você provavelmente vai querer mexer nesta query
 */
private static final String PEGAR_TABELAS = "select TABLE_NAME " +
        "from user_tables WHERE TABLE_NAME not like 'VW%' " +
        "and TABLE_NAME not like 'VM%'";

private static LinkedHashSet<String> comandos = new LinkedHashSet<String>();

public static void main(String[] args) throws ClassNotFoundException, SQLException {
    String driverName = "oracle.jdbc.driver.OracleDriver";
    Class.forName(driverName);
    Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@172.26.2.6:1521:SIVEFDESENV", "sivef_spread", "spread2006#");
   
    //pegar todas as tabelas que não sejam views
    List<String> tabelas = pegarTabelas(connection);
   
    //para cada tabela, quem é chave primária?
    for(String tabela:tabelas){
        recursao(connection, tabela);
    }
   
    connection.close();
   
    for(String comando:comandos){
        System.out.println(comando);
    }
}

private static void recursao(Connection connection, String tabela) throws SQLException {
    List<String> fks = pegarTabelasFKs(connection,tabela);
    for(String fk:fks){
        recursao(connection, fk);   
    }
    deletarTudoDaTabela(tabela);
}

private static void deletarTudoDaTabela(String tabela) {
    comandos.add("DELETE FROM "+tabela+";");
   
}

private static List<String> pegarTabelasFKs(Connection connection, String tabela) throws SQLException {
    return queryJDBC(connection,PEGAR_FK,tabela);
}

private static List<String> pegarTabelas(Connection connection) throws SQLException {
    return queryJDBC(connection,PEGAR_TABELAS,null);
}

private static List<String> queryJDBC(Connection connection,String query, String parametro) throws SQLException {
    List<String> tabelas = new ArrayList<String>();
    PreparedStatement ps = connection.prepareStatement(query);
    if (parametro != null){
        ps.setString(1, parametro);
    }
    ResultSet rs = ps.executeQuery();
    while(rs.next()){
        tabelas.add(rs.getString(1));
    }
    rs.close();
    ps.close();
    return tabelas;
}   

}