T-SQL, and cascading updates or finding constraint names

695 pts.
SQL Server
Standard Edition 2000
Hi all, 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. Steve.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Finding the constraints is the easy part. Just open Enterprise Manager and right click on the table. Under the menu [All Tasks] use the [Display Dependencies] utility. You’ll be able to find everything from here.

SQL server can also Cascade the updates for you. Right click on the table and select [Design table] then on the menu bar click [Manage Relationships] Select the foreign key constraint and click [Cascade update related fields].

I’d recommend you learn how to do this in SQL and create a stored proc to move them from OLD to New. Learning about Cursors and stored procs should be easy for you. You sound like you know what your doing.

Good luck

Discuss This Question: 1  Reply

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Sonotsky
    BeerMaker, et. al., Thanks for your feedback. I was already aware of how to find the constrains via EntMan, but what I really need is a way to programmatically query and return, via SQL, which tables have foreign keys, or check constraints, or triggers, etc. Since I posted my original question, I've done some digging and think I've made some progress. Here's my SQL: SELECT * FROM sysobjects WHERE ((parent_obj != '0') AND ((xtype = 'C') OR (xtype = 'D') OR (xtype = 'F') OR (xtype = 'IF') OR (xtype = 'TF') OR (xtype = 'TR') OR (xtype = 'UQ'))) ORDER BY parent_obj I found that the parent_obj field of a dependancy object is the table ID, in sysobjects, of the parent table (duh). I think all I have to do now is massage the results from this query and base/match the results to the list of selected tables returned by the previous query (see my original post). I know that I can also do a subquery and combine the two, but I need to have the list of tables separate for reporting purposes (how many tables were affected, records modified per table, etc.). If and when I figure this out, I will post a follow-up, but don't hold your breath. :)
    695 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: