Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question

1 Answer 1

up vote 5 down vote accepted

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).

share|improve this answer
    
While developing, to clear and set new test data. –  IvanP Apr 15 '13 at 19:35
    
@IvanP: Assuming this is part of an automated process, it's fairly simple to identify which tables have FKs. DROP those first, then recreate your schema in reverse order. –  Jon of All Trades Apr 15 '13 at 19:43
    
Script must be updated for every change in table, don't need that. –  IvanP Apr 15 '13 at 19:49
    
@IvanP what do you mean "every change in table"? As I said, it's very easy to automate this to cover all tables. I was just showing the basic approach for a single table. –  Aaron Bertrand Apr 15 '13 at 19:50
    
I thought you were talking about static script and recreating whole table structure. –  IvanP Apr 15 '13 at 21:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.