I’ve been asked to perform an upgrade for one of our vendors to a new version of our software. Unfortunately their data is still stored in the old DB format, and app pages. We figured the easier way to perform the upgrade would be to migrate their data to the new format, rather than trying to upgrade their existing version of the application.
Our plan of action involved taking an existing copy of the DB, stripping out any data, and then copying over the new data. The easiest way to clean up the data is to use
truncate table tablenamehere, and away goes the data. This is fine for most databases, however our developers have used foreign keys. This means that it specifically references data in other tables, if it’s not there, then it’ll get upset about it. This constraint stops you from issuing a truncate because it breaks all the relationships in the database. SQL simply throws a nice error message like this:
Msg 4712, Level 16, State 1, Line 1 Cannot truncate table 'sometable' because it is being referenced by a FOREIGN KEY constraint.
This obviously isn’t very helpful because it’s quite difficult to figure out which table references the table you’re trying to truncate. So I had to dig a little bit into Microsoft’s “sys tables”. Each database has a collection of sys tables. In these tables, a bunch of information about the databases tables, columns, indexes, and other objects is contained. From playing around, I built the following script:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
This code returns the destination table for the foreign key, the foreign key name, the field that the constraint is placed against, and the reference table, and column. From the results in this, it’s relatively easy to drop all the foreign keys with a small cursor, something like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
Now, if you’re sneaky, and want to rebuild all this foreign keys afterwards, instead of inserting them into a temporary table, you can put them in a more static table, at least until you’ve cleaned up all the tables. Once you’ve cleaned up the tables, you can reverse the drop, with another cursor… like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28
In both examples, I’ve printed the output to screen, rather than issued an
exec, but it gives you a few moments to review the output, and make sure it’s doing what you expect it to be.
All a bit of fun and games with system tables, and cursors.