Truncate a database with sp_MSforeachtabl in MS SQL Server


There may be occasion when you need to clear all data in MS SQL Database (the most sophisticated and user friendly RDBMS available today). Here is the quick guide.

Truncate with sp_MSforeachtable Stored Procedure

Access you MS SQL Server Studio or CTP. As we all know this can be get done the job with TRUNCATE command which delete data and release physical memory too. You have to perform the command for each table in database. But the MS SQL provide a better and easy way to do the task with a stored procedure.

Start a new SQL Query and add the following command to delete all data from tables.

USE MyDatabaseEXEC sp_MSforeachtable ‘TRUNCATE TABLE ?’

This will clear all data from table who has no foreign keys applied. You can alternatively use the following command which will only delete the contents and not the memory things.

USE MyDatabaseEXEC sp_MSforeachtable ‘DELETE FROM ?’

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.