Paris Polyzos' Blog

Let’s assume you have a database messing around with sample data and want an easy and fast way to delete all data.

What you can use:
sp_MSforeachtable: Stored Procedure to perform same actions for all tables in the specified database
DELETE: Removes rows from a table or view. More here
TRUNCATE TABLE name: Identical to DELETE statement with no Where clause. Both removes all rows in a table but TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.

Have in Mind
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint, so instead, use DELETE statement without a WHERE clause. The reason is that

TRUNCATE TABLE is not logged and it cannot activate a trigger.

-- Disable Referential Integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1 DELETE FROM ? else TRUNCATE TABLE ? '
GO
-- Enable Referential Integrity Again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

One of the greatest tool I daily use for sql is the SQL Prompt V4.0.

Key features of the tool is:

• Code Completion: Really fast and way better that SQL Management studio 2008 built in intellisence
• SQL Formatting based on your preferences
• Access to your database schema from within the editor
• Snippets in order to faster create Insert/Update/Delete statements, functions, stored procedures…

Some screenshots I took from their site:

Also this tool, compared to previous version, is very very fast and when make any modification to your original Schema, you just type Ctrl+Shift+D and all the context is being updated.

Give it a try!