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...
>> 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
>|||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...
> > 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
> >>
> >
> >
>
>