removing all data from all users table

Hi All,
I have over 200 tables in a database and I like to know
if there is a Transact-SQL Reference that would remove
all rows form ALL tables in the database. I am not sure
if this is possible because some tables are referenced by
foreign key constraints.
TRUNCATE TABLE name only does one table at a time and
it's too time consuming to use it to remove all rows from
all tables.
Thank you,
Mitra
You'll still have issues with foreign keys but you can do this:
EXEC sp_msForEachTable 'TRUNCATE TABLE ?'
Or you can generate a script this way:
SELECT 'TRUNCATE TABLE '+TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
When you run this, it will generate a series of statements in the lower
pane. You can copy this to the top pane (or new query window) and execute
it. You might consider adding a GO to the SELECT so that any errors on
individual truncate commands don't abort the whole batch; the list of errors
at the bottom will be your guide to tables you need to disable constraints
or otherwise handle individually.
You could also consider wiping out the database and re-creating it.
Hopefully you have scripts for all the tables/procedures etc. in SourceSafe,
and automating this should be trivial (especially if you're going to do this
periodically).
http://www.aspfaq.com/
(Reverse address to reply.)
"mitra fatholahi" <mitra928@.hotmail.com> wrote in message
news:17b6601c449e2$b99060e0$a601280a@.phx.gbl...
> Hi All,
> I have over 200 tables in a database and I like to know
> if there is a Transact-SQL Reference that would remove
> all rows form ALL tables in the database. I am not sure
> if this is possible because some tables are referenced by
> foreign key constraints.
> TRUNCATE TABLE name only does one table at a time and
> it's too time consuming to use it to remove all rows from
> all tables.
> Thank you,
> Mitra
|||Mitra,
If possible, cant you script out the entire database including the
indexes,triggers,relationships etc ,drop the database and then recreate the
database ?Since, as you mention the foreign key relationships exist, you
cannot do TRUNCATE TABLE unless you drop all those FKs and recreate them
later.Other option would be to prepare a DELETE FROM script on all tables,
making sure that the child tables appear first.I would adopt this only if I
cant drop the database.If you are going with DELETE, then you can do
something like:
SELECT 'DELETE FROM '+TABLE_SCHEMA+'.'+QUOTENAME(TABLE_NAME)+CHAR(13)+ 'GO'
FROM information_schema.tables
The above query would generate a script.Make necessary modifications and
execute the resultant script.If you are going with TRUNCATE you can adopt
the above script generation method.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"mitra fatholahi" <mitra928@.hotmail.com> wrote in message
news:17b6601c449e2$b99060e0$a601280a@.phx.gbl...
> Hi All,
> I have over 200 tables in a database and I like to know
> if there is a Transact-SQL Reference that would remove
> all rows form ALL tables in the database. I am not sure
> if this is possible because some tables are referenced by
> foreign key constraints.
> TRUNCATE TABLE name only does one table at a time and
> it's too time consuming to use it to remove all rows from
> all tables.
> Thank you,
> Mitra