Truncate all rows in Database
This is yet another fequently asked question
in the public newsgroups. Here is a simple script that will allow you to
do the same easily. In this script I've used the truncate clause rather than
the delete operation because trucate is faster and doesnot get into the logging
process that is time comsuming and resource comsuming wrt to hold the data for
a possible rollback.
-
DECLARE
@TruncateStatement nvarchar(2000)
-
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
-
FETCH
NEXT
FROM TruncateStatements INTO @TruncateStatement
-
-
WHILE
@@FETCH_STATUS = 0
-
BEGIN
-
FETCH
NEXT
FROM TruncateStatements INTO @TruncateStatement
-
EXEC(@TruncateStatement)
-
END
-
--
Clean up work
-
CLOSE
TruncateStatements
-
DEALLOCATE
TruncateStatements
The script for sure looks simple. But here are some restrictions and care you
need to take. You might need to run this multiple times. This is because the
script doesnot take care of the fact of parent-child relations i.e. if there is
a parent child relation then the child needs to be deleted before the parent
can be deleted. Hence such referencial ordering is NOT taken care by this
script. Hence you might need to run this query a multiple times.
Download the code
|