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]'')
TRUNCATE TABLE ?'
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.
