Truncate Tables that have Foreign Keys

One of the issues I often run into is the need to truncate very large tables. But if there is a foreign key constraint on the table, SQL Server will not allow a truncate, only a delete. This is fine for smaller tables but if the table has millions of rows, we want to do a truncate vs. a delete, as a truncate is minimally logged and much faster. Unfortunately, disabling the constraints is not enough. It has to be dropped. So I put together a script to do this for me.

Save the Definition

The first thing we need to do is document all the foreign keys so we can re-create them later. I do this by getting the constraint definitions from a couple of DMV’s and storing the pertinent data in a temporary table. Note that if for some reason you lost your connection, you would lose this table so you could give some consideration to using a permanent table.

 1:  SELECT
    2:      fk.name AS FKName
    3:      ,OBJECT_NAME(fk.parent_object_id) AS TableName
    4:      ,OBJECT_NAME(fk.referenced_object_id) AS ReferencesTable
    5:      ,COL_NAME(fk.parent_object_id,fkc.parent_column_id) AS ConstraintColumn
    6:      ,COL_NAME(fk.referenced_object_id,referenced_column_id) AS ReferenceColumn
    7:  INTO #fk
    8:  FROM sys.foreign_keys fk
    9:      INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
   10:  ORDER BY 1

Drop the Constraints

Now we can use dynamic SQL to drop the constraints

  1:  DECLARE 
   2:      @FKName sysname 
   3:      ,@TableName sysname 
   4:      ,@ReferencesTableName sysname 
   5:      ,@ConstraintColumn sysname 
   6:      ,@ReferenceColumn sysname 
   7:      ,@SQL NVARCHAR(4000) 
   8:   
   9:  DECLARE cur CURSOR FOR 
  10:      SELECT FKName, TableName FROM #fk 
  11:  OPEN cur 
  12:  WHILE 1=1 
  13:  BEGIN     
  14:      FETCH NEXT FROM cur INTO @FKName, @TableName 
  15:      IF @@fetch_status !=0 
  16:          BREAK 
  17:   
  18:      SET @SQL = 'alter table ' + @TableName + ' DROP CONSTRAINT ' + @FKName 
  19:      EXEC dbo.sp_executesql @SQL 
  20:  END     
  21:  CLOSE cur 
  22:  DEALLOCATE cur 
  23:  GO

Truncate the Tables

Using the undocumented stored procedure sp_msforeachtabled we can easily truncate all the tables now.

   1:  exec sp_MSforeachtable 'truncate table ?' 

Re-Create the Constraints

Finally, we use dynamic SQL to re-create the constraints

1:  DECLARE 
   2:      @FKName sysname 
   3:      ,@TableName sysname 
   4:      ,@ReferencesTableName sysname 
   5:      ,@ConstraintColumn sysname 
   6:      ,@ReferenceColumn sysname 
   7:      ,@SQL NVARCHAR(4000) 
   8:   
   9:  DECLARE cur CURSOR FOR 
  10:      SELECT * FROM #fk 
  11:  OPEN cur 
  12:  WHILE 1=1 
  13:  BEGIN     
  14:      FETCH NEXT FROM cur INTO @FKName, @TableName, @ReferencesTableName, 
  15:          @ConstraintColumn, @ReferenceColumn 
  16:      IF @@fetch_status !=0 
  17:          BREAK 
  18:   
  19:      SET @SQL = 'alter table ' + @TableName + ' ADD CONSTRAINT ' + @FKName + 
  20:          ' FOREIGN KEY(' + @ConstraintColumn + ') REFERENCES ' 
  21:          + @ReferencesTableName + '(' + @ReferenceColumn + ')' 
  22:      EXEC dbo.sp_executesql @SQL 
  23:  END     
  24:  CLOSE cur 
  25:  DEALLOCATE cur 
  26:  GO

Finally a quick script to check the final row counts

 1:  create table #rowcount (tablename varchar(128), rowcnt int) 
   2:   
   3:  exec sp_MSforeachtable 'insert into #rowcount select ''?'', count(*) from ?' 
   4:   
   5:  select * from #rowcount order by tablename 
   6:   
   7:  DROP TABLE #rowcount 
   8: