• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 660
  • Last Modified:

SQL Server: How to change a field length? (From very experienced user)

[AHU Key] column is present in 38 tables.Its size is 15
There are 56 FK relationships among the above tables.
Purpose: To change key's size to, say 50.
Why is this question here? Please see the attached image.

Finally the question itself:
Can anybody suggest a safe method of changing lengths of [AHU Key] in all 38 tables?

If anybody doesn't acknowledge what it is about, then please do not bother. Also please do not reply with trivialities like "delete all foreign keys and then restore them" (because "safe" above is a key word).
P.S. Live situation is about 4 times more complex (plus [CHi Key], [Hyd Key], [Met Key]).
Experts Exchange has already helped me to do it in my multiple MS Access databases.
-.jpg
0
midfde
Asked:
midfde
  • 3
  • 3
2 Solutions
 
AdamSenior DeveloperCommented:
So you know that all the foreign keys need to be dropped and recreated after all the columns have been altered - so your question really is: How do I do this safely?

First, prepare your script to do the actual work - dropping the keys, altering the tables and recreating the keys.
Next, make a copy of the database - a copy only back to restore to another database would be a good way of doing this, as you can repeatedly restore until you are happy with the upgrade process.
Once the upgrade script is working correctly, and you are happy that everything that needs to be changed has done so, connect a development version of an application that uses the affected tables and make sure it all works correctly. If you can write a test process that will use all the foreign keys - both for updates and queries, that would be the best.
Alternatively, you can write some SQL scripts that emulate the application to make sure all updates/inserted/deletes/selects are working correctly.
When you are 100% happy that the upgrade script is correct, you'll need some downtime so you can put the database into single user mode (http://msdn.microsoft.com/en-us/library/ms345598.aspx). Single user mode will ensure that no chnages are made to the data while the upgrade is being processed - no missing cascades
With the database is single user mode, run your script
Restore the database to normal multi-user mode, and do some additional testing - if you can't alter data for test purposes, then you'll have to stick to selects and joins.
0
 
midfdeAuthor Commented:
>>So you know that all the foreign keys need to be dropped and recreated after all the columns have been altered - so your question really is: How do I do this safely?
Right.
>>First, prepare your script to do the actual work - dropping the keys, altering the tables and recreating the keys.
This is actually what my question is about. How to make sure all attributes, cascades etc are recreated correctly? And I mean recreated (like in "copied"), not created anew by manual coding.

Thanks for so detailed instructions on running the task. Now I want to know which one.
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
AdamSenior DeveloperCommented:
As the author said:

Thanks for so detailed instructions on running the task. Now I want to know which one.

and

(because "safe" above is a key word).

While my answer may not have discovered the automated way of discovering all the keys to be disabled or re-enabled, it did detail how to apply it safely with no un-cascaded changes being made while the work was being done.

It doesn't offer a full solution, but does cover an important part of the requested 'safety' requirements, as the author acknowledged, and consequently deserves some points.
0
 
midfdeAuthor Commented:
Agree.
0
 
AdamSenior DeveloperCommented:
Thank you - always good to help people out.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now