How to truncate all tables except one in MS SQL

It is well-known that a SQL guru can truncate all tables. This is not something anyone is going to do in production. For while coding or testing, this might be a common practice.

To truncate all tables, use the following sql:

EXEC sp_MSforeachtable 'TRUNCATE TABLE ?'

However, what if you wanted to exclude one table. For example, if using Entity Framework, one might want to keep the __MigrationHistory table untouched.

EXEC sp_MSForEachTable 'if ("?" NOT IN ''[dbo].[__MigrationHistory]'')

I finally figured it out by learning how to query the values:

EXEC sp_MSforeachtable 'if ("?" NOT IN ("[dbo].[__MigrationHistory]"))
         SELECT "?"'

It took me a good hour to figure this out. The key was to quote the ? variable.

Leave a Reply

How to post code in comments?