Wednesday, December 7, 2011

Oracle Tip: Delete All Rows from All Tables in a Database

Query to delete all rows of each & every table in a database.

This will be useful if you want to clear your database for test environment.

OPEN CUR


FETCH FROM CUR INTO @TableName

WHILE @@Fetch_Status = 0
BEGIN
           SET @SQL_STR = 'DELETE FROM DBO.'+@TableName+''
           BEGIN TRY
                        BEGIN TRANSACTION

                                     EXEC (@SQL_STR)
                        COMMIT TRANSACTION
           END TRY

           BEGIN CATCH
                        ROLLBACK TRANSACTION
           END CATCH


           FETCH NEXT FROM CUR INTO @TableName
END
CLOSE CUR
DEALLOCATE CUR

Digg Google Bookmarks reddit Mixx StumbleUpon Technorati Yahoo! Buzz DesignFloat Delicious BlinkList Furl

0 comments : on " Oracle Tip: Delete All Rows from All Tables in a Database "