how can we delete all the contents in all the tables in a database?

Question

Is there any SQL command through which we can do this ?? I dont want the tables to be deleted ONLY the contents have to be deleted....

Answer 1

U can go for truncate command.

truncate table
Loop thru all the tables.

Answer 2

1. Script all tables in to single script
2. Drop all tables (include content)
3. Run script to generate table only

May be can help
to run script use SQL Enterprise manager
1. Right Click on DB
2. Select All Task
3. Select Generate SQL Script
4. Click Show All ==> Check only all table
5. Save in to
5. Click OK

to drop ALl table
1. Enterprise manager
2. Table
3. Select all user table = table own by user ( not system table)
4. Press delete
5. Click drop ALL

to create table again
1. Open Query Analyzer
2. Open Script generated at first step above (3 step)
3. Run

Answer 3

just drop the schema..that will drop all the tables in the schema..

type

drop schema_name;


in the command line window..

if you want to delete only I suggest you create a sql file which is of the syntax

delete from table1;
commit;
delete from table2
commit;
delete from table3;
commit;
........and so on...


save the sql file as some name like delete.sql in some easy location eg D drive

then in the command line window type @d:/delete.sql

Answer 4

For each table in your DB just issue one of the following commands:
delete from
or
delete * from

Answer 5

delete from