title: Truncate Table With Foreign Key
body: <p>I have one database having above 1000+ tables. I am creating fresh Blank setup. For that i have to truncate all the tables but some of the tables i do not wish to truncate so i created on table and stored the names in the table.</p>
  
  <pre><code>    ----------- Create hardcode table ----------------------
      Create table TblHardCodeTableNotToTruncate(TableName varchar(100))
      go
      insert into TblHardCodeTableNotToTruncate
      select 'TblHardCodeTableNotToTruncate'
      go
  
      ---- insert the table names which dont wish to truncate ------------
  Insert into TblHardCodeTableNotToTruncate
  select 'TblAccount'
  go
  Insert into TblHardCodeTableNotToTruncate
  select 'TblCity'
  go
  </code></pre>
  
  <p>etc, Following is the query i used to truncate all the tables except these inserted tables</p>
  
  <pre><code>------------------------- Truncate all the tables except the tables specified in the Hardcode table -------------------------
  
  EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
  
  GO
  
  EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
  
  GO
  
  DECLARE @TBLTEMP TABLE(TABLENAME VARCHAR(100))
  insert into @TBLTEMP
  select name from sysobjects where xtype = 'U'
  
  delete from @TBLTEMP where tablename in (
  select tablename from TblHardCodeTableNotToTruncate)
  
  DECLARE @SQLQUERY VARCHAR(MAX) =''
  DECLARE @INTCNT INT = 1
  
  DECLARE @TABLENAME VARCHAR(100) =''
  
  
  WHILE (SELECT COUNT(*) FROM @TBLTEMP) &gt; 0
  BEGIN
  
  
              select  top 1 @TABLENAME = TABLENAME from @TBLTEMP
              SET @SQLQUERY = 'Truncate table ' + @TABLENAME
              EXEC(@SQLQUERY)
              PRINT @SQLQUERY
      DELETE FROM @TBLTEMP WHERE TABLENAME = @TABLENAME   
  END
  go
  
  EXEC sp_MSForEachTable  'ALTER TABLE ? CHECK CONSTRAINT ALL'
  
  GO
  
  EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'
  go
  </code></pre>
  
  <p>But i get the error foreign key reference error. I know i need to drop constraint before doing it. But is there any way to do it as i cannot drop then truncate and again add each time with so many tables. Is there any unique script can we write in below which will drop constraint if exists,truncate table and add the dropped constraint again to the table again.</p>