Return to main dbmstools documentation page.
dbdrop is a tool to drop schema objects (tables, views, sequences) from a database, either directly or by producing an SQL script to be executed later. xml2ddl produces a drop script for the schema, but this is based on the XML definition of the schema - dbdrop instead examines the schema in the database itself, and works out what to drop (and in what order) from that.
By default, all objects in the database are dropped. However, this can be restricted by schema and/or by table.
Because this script needs to get at metadata for the database, it uses the dbmsjy wrapper and a JDBC driver. See the dbmsjy page for details.
You use the tool like this:
dbmsjy.py dbdrop.py [dbOptions] [generalOptions]
or, specifying JDBC details directly:
dbmsjy.py dbdrop.py [jdbcOptions] [generalOptions]
| -d dbms | Name of the DBMS. Required. Values recognised are: postgres7, postgres8, mssql, oracle, mysql, hsql |
| -D dbName | Name of the database to connect to. Required |
| -H dbHost | Name or IP address of the database host to connect to. Optional, defaults to localhost |
| -P dbPort | Port of the database host to connect to. Optional, defaults to the default port for the DBMS |
| -c driverClass | Fully-qualified name of the JDBC driver class, e.g. org.postgresql.Driver. If omitted, will be defaulted based on URL. Must be present in classpath, usually by placing in the jdbc-drivers directory |
| -j JDBC_URL | JDBC URL for connecting to the database, in the format expected by the JDBC driver, e.g. jdbc:postgresql://localhost:5432/PS30 Required |
| -e objectName | Specifically exclude this table, view or sequence (add a -e option for each object you want to exclude) |
| -f filepath | Output file path. If omitted, output to stdout |
| -h | Shows this screen |
| -i objectName | Specifically include this table, view or sequence (add a -i option for each object you want to include) |
| -p password | Password to use to connect to DB (if required) |
| -s schema | Schema name to export (defaults to all schemas) |
| -u username | Username to use to connect to DB (if required) |
| -v | Enable verbose logging (to stderr) |
| -x | Execute - rather than generating a script to drop objects, actually drop them using JDBC. |
| --version | Print version number and exit |
| --ignore-errors | |
| Ignore errors dropping objects (with -x) | |
| --no-tables | Don't drop tables (default is to include) |
| --no-views | Don't drop views (default is to include) |
| --no-sequences | Don't drop sequences (default is to include) |
See the dbmsjy page for details of default JDBC drivers and URLs etc.
Drop (directly) all objects from a local Postgres database called mydb:
$ dbmsjy.py dbdrop.py -u fred -p itsasecret -d postgres8 -D mydb -x
Generate an SQL script (to stdout) to drop 2 named tables from a local MySQL database:
$ dbmsjy.py dbdrop.py -i tableone -i table2 --no-views -u fred -p itsasecret \
-d mysql -D mydb
Generate an SQL script (to a specified path) to drop all objects in the specified schema from an Oracle database on host bigserver:
$ dbmsjy.py dbdrop.py -s theschema -f /tmp/dropItAll.sql \
-u fred -p itsasecret -d oracle -H bigserver -D mydb