Delete all data from user tables with a DELETE statement?

Hello,
I need an sql script that will do a 'DELETE FROM <table name>' against
are USER tables in a db.
It is for a small database that is getting a lot of test data.
How would I go about writing this?
Thanks,
TmuldWhy not take a FULL backup of the database when it is empty and then restore
that each time you wish to start over?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Tmuldoon" <tmuldoon@.spliced.com> wrote in message
news:1187131121.604441.257780@.z24g2000prh.googlegroups.com...
> Hello,
> I need an sql script that will do a 'DELETE FROM <table name>' against
> are USER tables in a db.
> It is for a small database that is getting a lot of test data.
> How would I go about writing this?
> Thanks,
> Tmuld
>|||Hi
Take a look at Dan's script
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE
_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
"Tmuldoon" <tmuldoon@.spliced.com> wrote in message
news:1187131121.604441.257780@.z24g2000prh.googlegroups.com...
> Hello,
> I need an sql script that will do a 'DELETE FROM <table name>' against
> are USER tables in a db.
> It is for a small database that is getting a lot of test data.
> How would I go about writing this?
> Thanks,
> Tmuld
>|||Something else you may want to try is to add the data source to a dotNet
project using the project options in NET 2005 (if you have it) and add a
setup project to the database you add to the project. The setup project can
be uninstalled and reinstalled and this will refresh the data each time
around.
--
Regards,
Jamie
"Tmuldoon" wrote:

> Hello,
> I need an sql script that will do a 'DELETE FROM <table name>' against
> are USER tables in a db.
> It is for a small database that is getting a lot of test data.
> How would I go about writing this?
> Thanks,
> Tmuld
>|||generating scripts for execution from system objects is a GREAT way to do
stuff like this. 2 things here tho:
1) You can't use truncate on tables with FKs.
2) You can avoid the cursor by creating the output and pasting it for
execution thusly:
select 'delete from ' + name + '
go'
from sys.objects --cheating here - I hate typing out the infoschema stuff
:-))
where type = 'u'
execute that, then copy the output into the query window and execute it.
TheSQLGuru
President
Indicium Resources, Inc.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23zuqAdw3HHA.2312@.TK2MSFTNGP06.phx.gbl...
> Hi
> Take a look at Dan's script
> DECLARE @.TruncateStatement nvarchar(4000)
> DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
> FOR
> SELECT
> N'TRUNCATE TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND
> OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE
_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
> OPEN TruncateStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
> IF @.@.FETCH_STATUS <> 0 BREAK
> RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
> EXEC(@.TruncateStatement)
> END
> CLOSE TruncateStatements
> DEALLOCATE TruncateStatements
> "Tmuldoon" <tmuldoon@.spliced.com> wrote in message
> news:1187131121.604441.257780@.z24g2000prh.googlegroups.com...
>|||That would have been the smarted idea when I started...
Will do!
Thanks,
Tmuld|||It might be quicker to generate a script to create the FK constraints, drop
the FKs, truncate the tables and put the FKs back on.
The truncate table script can be created using the sysobjects table also
1;it
is quicker than using INFO SCHEMA ]
The 'delete from' statement would log everything taking much more time.
Thank you for sharing your knowledge
"TheSQLGuru" wrote:

> generating scripts for execution from system objects is a GREAT way to do
> stuff like this. 2 things here tho:
> 1) You can't use truncate on tables with FKs.
> 2) You can avoid the cursor by creating the output and pasting it for
> execution thusly:
> select 'delete from ' + name + '
> go'
> from sys.objects --cheating here - I hate typing out the infoschema stuff
> :-))
> where type = 'u'
> execute that, then copy the output into the query window and execute it.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23zuqAdw3HHA.2312@.TK2MSFTNGP06.phx.gbl...
>
>