dbdrop - Drop Schema Objects

Contents

Return to main dbmstools documentation page.

Introduction

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.

Prerequisites

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.

Usage

You use the tool like this:

dbmsjy.py dbdrop.py [dbOptions] [generalOptions]

or, specifying JDBC details directly:

dbmsjy.py dbdrop.py [jdbcOptions] [generalOptions]

Database Options:

-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

JDBC Options:

-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

General Options:

-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.

Long Options

--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.

Examples

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