Thursday, 3 April 2014

Truncate all tables in MSSQL excluding some

This command will truncate all tables in a mssql server database, which are not include in the if statement. Make sure to disable constraints before running this command. The example below
will truncate all tables except dbo.Table1, dbo.Table2 and dbo.Table3

-- truncate tables excluding ones in if statement
exec sp_msforeachtable '
  IF (''[dbo].[TABLE1]'' = ''?''
   or ''[dbo].[TABLE2]'' = ''?'' 
   or ''[dbo].[TABLE3]'' = ''?'' 
  
  )
    print ''NOT TRUNCATED ?''
  ELSE  
    print ''TRUNCATE TABLE ?;''
    TRUNCATE TABLE ?;
'

No comments:

Post a Comment