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 ?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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.
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
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).