Sorting DB tables from least dependent to most dependent

Question

Hi community,

I'm performing a data migration and the database I'm using only allows to export and import each table separately. In such a setup importing becomes a problem since the order in which tables are imported is important (you have to import referenced tables before referencing ones).

Is there any external tool that allows me to list the database tables sorted from least dependent to most dependent?

Thanks in advance.

Answer 1

This code helped me solve this problem. It sorts the tables from least dependent to most dependent in accordance with their FK relations read from the DB metadata.

public class Main {
    public static void main(String[] args) throws SQLException {
        DriverManager.registerDriver(new TimesTenDriver());
        Connection c = ...;
        DatabaseMetaData md = c.getMetaData();

        final ResultSet rawTables = md.getTables(null, "<your schema>", "%", null);

        List<Table> tables = new ArrayList<Table>();

        while (rawTables.next()) {
            final String tableName = rawTables.getString("TABLE_NAME");
            Table table = new Table(tableName);

            ResultSet rawKeys = md.getImportedKeys(null, "<your schema>", tableName);

            while (rawKeys.next()) {
                table.refs.add(rawKeys.getString("PKTABLE_NAME"));
            }

            rawKeys.close();

            tables.add(table);
        }

        rawTables.close();
        c.close();

        LinkedList<List<Table>> layers = new LinkedList<List<Table>>();

        while (tables.size() > 0) {
            List<Table> indep = new ArrayList<Table>();
            for (Table o : tables) {
                indep.add(o);
                for (Table i : tables) {
                    if (i.refs.contains(o.name)) {
                        indep.remove(o);
                        break;
                    }
                }
            }

            layers.add(indep);

            for (Iterator<Table> it = tables.iterator(); it.hasNext(); ) {
                Table t = it.next();
                if (indep.contains(t)) {
                    it.remove();
                }
            }
        }

        for (ListIterator<List<Table>> it = layers.listIterator(layers.size()); it.hasPrevious(); ) {
            final List<Table> layer = it.previous();

            for (Table table : layer) {
                System.out.println("ttbulkcp -i <your DSN> <your schema>." + table + " " + table);
            }
        }
    }

    private static class Table {
        public final String name;
        public final Set<String> refs;

        public Table(String name) {
            this.name = name;
            this.refs = new HashSet<String>();
        }
    }
}