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

Toad Data Modeler
1

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

2

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

cascade_delete.png
cascade_delete.png576×563 25.7 KB

3

Thanx, these are possible solutions.

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

Regards,
Andreas

4

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

5

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;
}

}