Is there a way to truncate table that has foreign a key?

Question

Is there a way to truncate table that has foreign keys ? Delete and reseed can take too long. Is deleting and recreating keys only way ? If so is there a tool that does this ?

Answer 1

No, you either need to delete and re-create the keys, or wait for the delete and re-seed. Disabling the foreign key temporarily might make the delete faster, but it still won't allow a truncate.

ALTER TABLE [dbo].[tablename] NOCHECK CONSTRAINT ALL;

-- delete, reseed, etc.

ALTER TABLE [dbo].[tablename] WITH CHECK CHECK CONSTRAINT ALL;

Very easy to automate this by building dynamic SQL from the metadata tables, depending on exactly which table(s) you need to target. The above is just a sample to demonstrate how it is done for a single table. For example, this will do so for each table that is the target of a foreign key and has an IDENTITY column:

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'SET NOCOUNT ON;';

;WITH s(t) AS
 (
   SELECT 
     QUOTENAME(OBJECT_SCHEMA_NAME(referenced_object_id)) 
     + '.' + QUOTENAME(OBJECT_NAME(referenced_object_id))
  FROM sys.foreign_keys AS k
  WHERE EXISTS 
  (
    SELECT 1 FROM sys.identity_columns 
    WHERE [object_id] = k.referenced_object_id
  )
  GROUP BY referenced_object_id
)
SELECT @sql = @sql + N'
  ALTER TABLE ' + t + ' NOCHECK CONSTRAINT ALL;
  DELETE ' + t + ';
  DBCC CHECKIDENT(''' + t + ''', RESEED, 0) WITH NO_INFOMSGS;
  ALTER TABLE ' + t + 'WITH CHECK CHECK CONSTRAINT ALL;'
FROM s;

PRINT @sql;
-- EXEC sp_executesql @sql;

It may be that the output gets truncated, but this is just a limitation of PRINT (8K) - the actual command is complete.

PS this assumes SQL Server 2005 or better. Always useful to specify your version as part of your question (usually with a tag).