Saturday, April 05 2014
Keywords: Database.ca, SQL Server Database, Tables in descending dependency order, tables ordered by foreign key dependency, delete sql tables in order of foreign key dependency
View & Delete SQL Server tables by their descending dependency order, not to violate foreign keys
All database developers and administrators, as well as QA staff have always needed to be able to wipe out all data in an entire test/dev database.
We have each contrived to accomplish this in our own way... either by deleting the data in one-table-at-a-time fashion, or by using a script (as I will be demonstrating) or through some third party tools that do this. Some QA staff make the whole process much easier by just dropping the database and re-creating it in the first place. But that might not be a good solution for everyone.
I have created two stored procedures:
The first one, called TablesInDependencyOrder, will simply show you all the tables in the database in the order that you choose. If you omit to provide the sorting order, then descending order will be the default. You could also optionally provide an XML valued parameter to this procedure for the tables that you would want it to bypass/omit... by default the system table sysdiagrams is omitted.
The second, called NukeDatabase, will delete all data pertaining to all tables in the database. It will achieve this by deleting them in their descending dependency order of course. Just like the first procedure, you would be able to provide it with an XML "omittedtable" parameter if you do not want it to delete data in certain tables. Before deleting important data in a production type environment make sure you test out this functionality well first.
You could download the scripts used in this article, at the bottom of this page.
Written by Montreal DBA Team