How to empty a SQL database?

I'm searching for a simple way to delete all data from a开发者_如何学编程 database and keep the structure (table, relationship, etc...). I using postgreSQL but I think, if there a command to do that, it's not specific to postgres.

Thanks,

Damien

Dump the schema using pg_dump. drop the database, recreate it and load the schema.

Dump you database schema (the -s tag) to a file:

pg_dump -s -f db.dump DB-NAME

Delete the database:

dropdb DB-NAME

Recreate it:

createdb DB-NAME

Restore the schema only:

pg_restore db.dump > psql DB-NAME

This should work on PostgreSQL; Other DBMS might have their own tools for that. I do no know of any generic tool to do it.

EDIT:

Following comments, you might want to skip the dropdb command, and simply create another database with the dumped schema. If all went through well, you can drop the old database:

pg_dump -s -f db.dump DB-NAME
createdb DB-NEW-NAME
pg_restore db.dump > psql DB-NEW-NAME

At this point, you have the full database at DB-NAME, and an empty schema at DB-NEW-NAME. after you're sure everything is OK, use dropdb DB-NAME.

You can do something like this:

export PGUSER=your_pg_user
export PGHOST=database.host
export PGPORT=port
export PGDATABASE=your_database

psql -qAtX -c "select 'TRUNCATE table ' || quote_ident(table_schema) || '.' || quote_ident(table_name) || ' CASCADE;' from information_schema.tables where table_type = 'BASE TABLE' and not table_schema ~ '^(information_schema|pg_.*)$'" | psql -qAtX

It will do what's necessary.

Of course these exports are not necessary, but they will make it simpler to run 2 copies of psql without having to givem them all standard -U, -d, and so on, switches.

One thing though - using TRUNCATE to do so, while faster than DELETE, has it's drowbacks - for example - it is not being replicated by Slony and any other replication system that works on triggers. Unless you are working on PostgreSQL 8.4, and your replication knows how to use triggers on TRUNCATE.

I'm not a Postgres guy, but one option would be to iterate through the tables and issue a Truncate command against each one. You'll have to take otable relationships into account, though - you will not be able to delete reference data before data that refers to it, for example.

In pgAdmin you can do:

• Right-click database -> backup, select "Schema only"
• Drop the database
• Create a new database and name it like the former
• Right-click the new database -> restore -> select the backup, select "Schema only"

your can delete all records of your database without restriction of foreign keys by following three steps

1. Take script of your Database
   1. Right Click on your database (your DB Name)
   2. click on task and then "Generate script"
   3. Specify location
2. Delete your database base
3. recreate a database with the same name and run you generated script

This way you can empty all of your database