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,
Tmuld
Why 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.googlegr oups.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.googlegr oups.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.googlegr oups.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 [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...
>
>
sql