sql-serversql-server-2008-r2sp-msforeachtable

How to delete data in all tables from a database except except few tables

I have 50+ tables in my database and I want to delete all the data in 48 tables.

I tried using 

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'

EXEC sp_MSForEachTable 'DELETE FROM ?  '

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'

EXEC sp_MSFOREACHTABLE 'SELECT * FROM ?'

GO

but it deletes all the data in all the tables.

Can some one suggest me what changes I need to make to delete data from all tables except TABLE1, TABLE2 etc in the query: 

EXEC sp_MSForEachTable 'DELETE FROM ?  '

I am using SQL Server 2008R2.

Solution

  • Use IF ''?'' NOT IN (''TABLE1'',''TABLE2'') before your DELETE FROM [?]
    By the way, I suggest using TRUNCATE TABLE [?] instead of DELETE FROM [?]