I have been tasked with a difficult project.
My employer has a rather large database, hosted on SQL Server 2000, which is used by several thousand users across our organization, and grouped by business units. This database is evolving with each minor release, which is just about every month.
On occasion, I'm asked to merge users from one business unit into another. This is not a much problem, since it's just a matter of searching for all instances of the business unit number and updating it with the new number.
When users in the "old" buisness unit move to the "new" unit, they must be able to access their historical data. Again, for the most part, not a problem. The catch is this: Some of the historical tables have foreign key constraints that have to be circumvented without recreating the tables.
I'm sure most, if not all of you reading this, are saying "what's the big deal? Just look up the name of the constraint and NOCHECK it!" My problem, for which I turn to my esteemed peers, is that I want to script this merging activity, so I don't have to do the scut-work of determining table and contraint names every time the merge is requested, because the database objects have changed.
I have a Perl script which so far is able to determine the table names that have to be updated, but what I need to know, in SQL language, is:
a) how to CASCADE updates, without modifying the tables in any kind of permanent way, or
b) how to find out if there are any constraints on a particular table, and if so, what the name is.
Once I know one of these two, I can loop through each table that the script would've already leanred, and update all instances of the business unit number without having SQL Server moan and complain about updating with a constraint in place.
Any help or suggestions would be greatly appreciated.