Home > IT Answers > SQL Server > T-SQL, and cascading updates or finding const...
Help

Question

  Asked: Feb 15 2005   10:09 AM GMT
  Asked by: sonotsky

T-SQL, and cascading updates or finding constraint names


SQL Server, Standard Edition 2000, SQL

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.

Subscribe to Alerts! Get questions and answers delivered to your Inbox.


E-mail me updates on this question



   SUBSCRIBE

hidden modal window
Help

Answer Wiki (Improve, edit or add to this answer)

IMPROVE THIS ANSWER
View History
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
Last Answered: Feb 15 2005  11:10 AM GMT  by BeerMaker




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
  • AddThis Social Bookmark Button

Browse more Questions and Answers on SQL Server and Development.

Looking for relevant SQL Server Whitepapers? Visit the SearchSQLServer.com Research Library.


RSS - Discussions Help

Discuss This Answer


You must be logged-in to discuss a question. Log-in/Register

sonotsky  |   Feb 18 2005  9:12AM GMT

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. :)