The two links in your post point to scripts that generate the commands necessary to drop and recreate all your current foreign keys (I haven't tested them, just glanced through for intent).
However, in both cases the scripts place those commands in a variable, and print them. Neither script (as shown) automatically executes those commands.
This seems wise to me - I would want to look at the scripts generated, try to understand them, and maybe even compare them to the constraints in a CREATE TABLE
script generated by SSMS, to make sure they seem to be complete.
The first link (to a DBA.SE question) would let you DELETE
the rows in your table, not actually TRUNCATE
, so we'll ignore that one.
In the MSDN link, to actually drop your foreign keys, you'd need to execute this command after populating @strsql
with the necessary commands to do the drop:
EXEC sp_executesql @strsql;
If this is your actual problem, then you need to be very careful to test this all very thoroughly, on a test database, that you can easily restore if you need to.
You must capture the necessary commands to recreate your foreign keys before you drop them. If you ran the necessary commands to drop the foreign keys, then tried to put the commands to recreate them in @strsql
(which is what the second chunk of code in the MSDN link does) - you won't get anything, because your foreign keys will already have been dropped.
Make sure to capture the commands you need to recreate the foreign keys before you drop them. Also, make sure you put the "drop" commands in a different variable than the commands to recreate the foreign keys, since you need to have both sets of commands available before you drop the foreign keys.
The proper order of operation would be:
- Generate the code to recreate the foreign keys (maybe in to a variable
@FK_Create
- Generate the code to drop the foreign keys (into a variable
@FK_Drop
)
EXECUTE sp_executesql @FKDrop
TRUNCATE
your tables.
EXECUTE sp_executesql @FKCreate
TRUNCATE TABLE
commands; and recreate the keys. OP explicitly says they don't want toDROP
orDELETE
from their tables; OP does not say they object to removing and recreating the constraints. The answer in that so-called duplicate does not delete and recreate the foreign key constraints. Note that question also talks about reseeding - the "keys" being deleted are the rows in the table, not the constraints.