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 ?’

Published by


A developer,teacher and a blogger obsessed with Python,Dart and open source world from India

Leave a Reply

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

You are commenting using your 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.