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 ?
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
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
PS this assumes SQL Server 2005 or better. Always useful to specify your version as part of your question (usually with a tag).