database cleanup

is there any SQL statement that can clear all rows in each table in a databa
se?Hi,
There is no single command to clean all the tables. You need to rite an
iterative loop traversing all the tables in the database.
For better performance try using TRUNCATE TABLE instead of DELETE
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"nonno" wrote:
> is there any SQL statement that can clear all rows in each table in a database?[/c
olor]|||Here is how you can do it.
Create a cursor on table sysobjects so that you select all the tables.
Traversing each row, create a query to TRUNCATE dynamically an then execute
the query.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Chandra" wrote:
> Hi,
> There is no single command to clean all the tables. You need to rite an
> iterative loop traversing all the tables in the database.
> For better performance try using TRUNCATE TABLE instead of DELETE
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "nonno" wrote:
>|||You can use the sp_msforeachtable stored procedure:
exec sp_msforeachtable 'truncate table ?'
Notice that sp_msforeachtable is a none documented stored procedure
(but it is very well known and you can find articles that describe it
with google search). Also if you have foreign keys in the DB, then
truncate can not be used for the parent tables.
Adi|||In addition , check out FKs created on the tables.
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
"Adi" <adico@.clalit.org.il> wrote in message
news:1117006995.155155.232830@.z14g2000cwz.googlegroups.com...
> You can use the sp_msforeachtable stored procedure:
> exec sp_msforeachtable 'truncate table ?'
> Notice that sp_msforeachtable is a none documented stored procedure
> (but it is very well known and you can find articles that describe it
> with google search). Also if you have foreign keys in the DB, then
> truncate can not be used for the parent tables.
> Adi
>|||http://www.extremeexperts.com/SQL/S...eAllTables.aspx
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"nonno" <nonno@.discussions.microsoft.com> wrote in message
news:BBED9F55-36ED-4D65-9FD0-95F031F4B42F@.microsoft.com...
> is there any SQL statement that can clear all rows in each table in a
database?|||Generate a script for all the tables in the database, and then run it. This
will drop / create all tables.
"nonno" <nonno@.discussions.microsoft.com> wrote in message
news:BBED9F55-36ED-4D65-9FD0-95F031F4B42F@.microsoft.com...
> is there any SQL statement that can clear all rows in each table in a
database?sql