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

How to delete all rows in a DataBase

I have a database, I want to delete all rows in all tables. Only remain the
schema of all tables.
I only know to use Sql command like 'delete from aTable' to every tables.
Have there any convenient way to do that?
ad
Perhaps you need to deal with DRI before your the 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
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23jGv2h4gFHA.3436@.tk2msftngp13.phx.gbl...
> I have a database, I want to delete all rows in all tables. Only remain
the
> schema of all tables.
> I only know to use Sql command like 'delete from aTable' to every
tables.
> Have there any convenient way to do that?
>
|||Easiest way it probably to script the database (actually, you should have the schema as source code
already). Then drop the database and run the script file(s).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ad" <ad@.wfes.tcc.edu.tw> wrote in message news:%23jGv2h4gFHA.3436@.tk2msftngp13.phx.gbl...
>I have a database, I want to delete all rows in all tables. Only remain the
> schema of all tables.
> I only know to use Sql command like 'delete from aTable' to every tables.
> Have there any convenient way to do that?
>

How to delete all rows in a DataBase

I have a database, I want to delete all rows in all tables. Only remain the
schema of all tables.
I only know to use Sql command like 'delete from aTable' to every tables.
Have there any convenient way to do that?ad
Perhaps you need to deal with DRI before your the 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
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23jGv2h4gFHA.3436@.tk2msftngp13.phx.gbl...
> I have a database, I want to delete all rows in all tables. Only remain
the
> schema of all tables.
> I only know to use Sql command like 'delete from aTable' to every
tables.
> Have there any convenient way to do that?
>|||Easiest way it probably to script the database (actually, you should have th
e schema as source code
already). Then drop the database and run the script file(s).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ad" <ad@.wfes.tcc.edu.tw> wrote in message news:%23jGv2h4gFHA.3436@.tk2msftngp13.phx.gbl...[v
bcol=seagreen]
>I have a database, I want to delete all rows in all tables. Only remain the
> schema of all tables.
> I only know to use Sql command like 'delete from aTable' to every tables
.
> Have there any convenient way to do that?
>[/vbcol]

How to delete all rows in a DataBase

I have a database, I want to delete all rows in all tables. Only remain the
schema of all tables.
I only know to use Sql command like 'delete from aTable' to every tables.
Have there any convenient way to do that?ad
Perhaps you need to deal with DRI before your the 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
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23jGv2h4gFHA.3436@.tk2msftngp13.phx.gbl...
> I have a database, I want to delete all rows in all tables. Only remain
the
> schema of all tables.
> I only know to use Sql command like 'delete from aTable' to every
tables.
> Have there any convenient way to do that?
>|||Easiest way it probably to script the database (actually, you should have the schema as source code
already). Then drop the database and run the script file(s).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ad" <ad@.wfes.tcc.edu.tw> wrote in message news:%23jGv2h4gFHA.3436@.tk2msftngp13.phx.gbl...
>I have a database, I want to delete all rows in all tables. Only remain the
> schema of all tables.
> I only know to use Sql command like 'delete from aTable' to every tables.
> Have there any convenient way to do that?
>