3

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.

P P P
  • 227
  • 3
  • 16
Maltesh
  • 383
  • 2
  • 6
  • 14

1 Answers1

3

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

Hybris95
  • 2,286
  • 2
  • 16
  • 33