This response is a textual copy of: Is there a way to truncate table that has foreign to key?
You need to delete and recreate the keys, or wait for DELETE and re-fill them. Disabling the foreign key temporarily can make the DELETE faster, but it will continue without letting you do the truncate
ALTER TABLE [dbo].[tablename] NOCHECK CONSTRAINT ALL;
-- borrar, rellenar, etc.
ALTER TABLE [dbo].[tablename] WITH CHECK CHECK CONSTRAINT ALL;
The easy to automate this by building a dynamic SQL from the metadata tables, making it work exactly for the tables you need. For example, this will do so for each table that has a foreign key and a 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;
The output may be truncated, but this is a limitation of the PRINT (8K), the current command is complete.
