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