how to delete all the data in the sql server?

Hi,
Is there a way that i can delete all the data from
all the tables in the sql server, i want to remain the
table's structure and user define function and i dun want
to delete the whole database.
Thank you.
regards,
florenceNo command per se. You'd have to execute a DELETE or TRUNCATE TABLE
statement against all tables. Such is easy to generate or automate (using a
cursor), however you have to consider foreign keys so you do it in the right
order. Or you can run the statements several times.
I suggest that you generate script for your DDL. You really should have your
DDL in a script file (or one file per object) anyhow, so you can generate
script, tidy up and comment the script. You can then drop the database and
re-create it from that script file.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"florence" <florencelee@.visualsolutions.co.my> wrote in message
news:04c201c3a6ad$d155a5b0$a501280a@.phx.gbl...
> Hi,
> Is there a way that i can delete all the data from
> all the tables in the sql server, i want to remain the
> table's structure and user define function and i dun want
> to delete the whole database.
> Thank you.
> regards,
> florence|||Hi Florence.
You might find it easier to simply script the database schema (including
stored procs, functions etc), using the SQL Enterprise Manager's scripting
tools, then dropping the database & creating an empty one from the script.
The bigger the database, the more effective this approach will be.
Regards,
Greg Linwood
SQL Server MVP
"florence" <florencelee@.visualsolutions.co.my> wrote in message
news:04c201c3a6ad$d155a5b0$a501280a@.phx.gbl...
> Hi,
> Is there a way that i can delete all the data from
> all the tables in the sql server, i want to remain the
> table's structure and user define function and i dun want
> to delete the whole database.
> Thank you.
> regards,
> florence|||Hi,
Execute this command in SQL Analyzer, Replace dbname with your database
name. This commands will delete all the data from all the user tables.
Careful !!! Truncate is not a logged operation, After issuing this command
you will not be able rollback the data.
use dbname
go
declare @.x nvarchar(255)
set @.x=''
select @.x = @.x+' truncate table ' + name
from sysobjects where type='U'
exec sp_executesql @.x
go
Thanks
Hari
MCDBA
"florence" <florencelee@.visualsolutions.co.my> wrote in message
news:04c201c3a6ad$d155a5b0$a501280a@.phx.gbl...
> Hi,
> Is there a way that i can delete all the data from
> all the tables in the sql server, i want to remain the
> table's structure and user define function and i dun want
> to delete the whole database.
> Thank you.
> regards,
> florence|||Truncate table won't work for tables that are referred to by foreign keys.
> Careful !!! Truncate is not a logged operation, After issuing this command
> you will not be able rollback the data.
Truncate table is logged, and can be rolled back:
use tempdb
go
create table t(c1 int identity)
insert t default values
insert t default values
insert t default values
go
select * from t
begin tran
truncate table t
select * from t
rollback
select * from t
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eogxJmtpDHA.2592@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Execute this command in SQL Analyzer, Replace dbname with your database
> name. This commands will delete all the data from all the user tables.
> Careful !!! Truncate is not a logged operation, After issuing this command
> you will not be able rollback the data.
>
> use dbname
> go
> declare @.x nvarchar(255)
> set @.x=''
> select @.x = @.x+' truncate table ' + name
> from sysobjects where type='U'
> exec sp_executesql @.x
> go
> Thanks
> Hari
> MCDBA
>
> "florence" <florencelee@.visualsolutions.co.my> wrote in message
> news:04c201c3a6ad$d155a5b0$a501280a@.phx.gbl...
> > Hi,
> >
> > Is there a way that i can delete all the data from
> > all the tables in the sql server, i want to remain the
> > table's structure and user define function and i dun want
> > to delete the whole database.
> >
> > Thank you.
> >
> > regards,
> >
> > florence
>|||You may want to generate a script to first drop all the foreign keys and
generate another script to then add the foreign keys back. Run the first
script before the TRUNCATE TABLE statements, and then run the second script
afterwards.
Also, I'd probably re-write the script as follows (with some minor changes):
use dbname
go
declare @.x varchar(8000)
set @.x=''
select @.x = @.x+' truncate table ' + QUOTENAME(name) + char(13) + char(10)
from sysobjects where type='U'
exec sp_executesql @.x
go
Note that varchar(8000) still may not be enough.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eogxJmtpDHA.2592@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Execute this command in SQL Analyzer, Replace dbname with your database
> name. This commands will delete all the data from all the user tables.
> Careful !!! Truncate is not a logged operation, After issuing this command
> you will not be able rollback the data.
>
> use dbname
> go
> declare @.x nvarchar(255)
> set @.x=''
> select @.x = @.x+' truncate table ' + name
> from sysobjects where type='U'
> exec sp_executesql @.x
> go
> Thanks
> Hari
> MCDBA
>
> "florence" <florencelee@.visualsolutions.co.my> wrote in message
> news:04c201c3a6ad$d155a5b0$a501280a@.phx.gbl...
> > Hi,
> >
> > Is there a way that i can delete all the data from
> > all the tables in the sql server, i want to remain the
> > table's structure and user define function and i dun want
> > to delete the whole database.
> >
> > Thank you.
> >
> > regards,
> >
> > florence
>|||Thanks a lot Tiber for the information.
I was under the impression that DDL will automatically commit the
transactions.
Thanks
Hari
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OYvZ3rtpDHA.2188@.TK2MSFTNGP11.phx.gbl...
> Truncate table won't work for tables that are referred to by foreign keys.
>
> > Careful !!! Truncate is not a logged operation, After issuing this
command
> > you will not be able rollback the data.
> Truncate table is logged, and can be rolled back:
> use tempdb
> go
> create table t(c1 int identity)
> insert t default values
> insert t default values
> insert t default values
> go
> select * from t
> begin tran
> truncate table t
> select * from t
> rollback
> select * from t
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eogxJmtpDHA.2592@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > Execute this command in SQL Analyzer, Replace dbname with your database
> > name. This commands will delete all the data from all the user tables.
> >
> > Careful !!! Truncate is not a logged operation, After issuing this
command
> > you will not be able rollback the data.
> >
> >
> > use dbname
> > go
> > declare @.x nvarchar(255)
> > set @.x=''
> > select @.x = @.x+' truncate table ' + name
> > from sysobjects where type='U'
> > exec sp_executesql @.x
> > go
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> > "florence" <florencelee@.visualsolutions.co.my> wrote in message
> > news:04c201c3a6ad$d155a5b0$a501280a@.phx.gbl...
> > > Hi,
> > >
> > > Is there a way that i can delete all the data from
> > > all the tables in the sql server, i want to remain the
> > > table's structure and user define function and i dun want
> > > to delete the whole database.
> > >
> > > Thank you.
> > >
> > > regards,
> > >
> > > florence
> >
> >
>