How do I extract the SQL statements required to move all tables and views from an existing database to another database?
Boy, this is a big one. :) The operation is performed in 9 steps:
- Open a connection to the source database. Use the DriverManager class.
- Find the entire physical layout of the current database. Use the DatabaseMetaData interface.
- Create DDL SQL statements for re-creating the current database structure. Use the DatabaseMetaData interface.
- Build a dependency tree, to determine the order in which tables must be setup. Use the DatabaseMetaData interface.
- Open a connection to the target database. Use the DriverManager class.
- Execute all DDL SQL statements from (3) in the order given by (4) in the target database to setup the table and view structure. Use the PreparedStatement interface.
- If (6) threw exceptions, abort the entire process.
- Loop over all tables in the physical structure to generate DML SQL statements for re-creating the data inside the table. Use the ResultSetMetaData interface.
- Execute all DML SQL statements from (8) in the target database.