What order to be followed while generating schema upgrade script?
-
I've two databases consider as OldVersion and NewVersion. What I would like to do is that I need to compare the database for DDL changes (schema changes such as column difference, datatype difference, table difference). I would like to compare and generate a script such a way that it will upgrade the OldVersion database same as NewVersion database. This is not a straight way to do that. Say for example, if a column is FK, that column could not be altered. This is one constraint. Likewise what are the possible constraints to generate the script? How could I resolve them? To generate the script for entire database in what order I've to work on. I look for answer such as Drop Index, Drop Constraints, Drop Columns or Alter Column, Alter Index, Add Contraints in particular order. I know I've some tools in place for that. But I would like to generate it. The question is lengthier one. Please bear with me and provide your solution.
-
I've two databases consider as OldVersion and NewVersion. What I would like to do is that I need to compare the database for DDL changes (schema changes such as column difference, datatype difference, table difference). I would like to compare and generate a script such a way that it will upgrade the OldVersion database same as NewVersion database. This is not a straight way to do that. Say for example, if a column is FK, that column could not be altered. This is one constraint. Likewise what are the possible constraints to generate the script? How could I resolve them? To generate the script for entire database in what order I've to work on. I look for answer such as Drop Index, Drop Constraints, Drop Columns or Alter Column, Alter Index, Add Contraints in particular order. I know I've some tools in place for that. But I would like to generate it. The question is lengthier one. Please bear with me and provide your solution.
We use Red-Gate for this and it uses the dependency relationships to generate the scrip. However on a large change it can screw up and needs to be broken down, I do the tables, then the views and then the procedures.
Never underestimate the power of human stupidity RAH
-
We use Red-Gate for this and it uses the dependency relationships to generate the scrip. However on a large change it can screw up and needs to be broken down, I do the tables, then the views and then the procedures.
Never underestimate the power of human stupidity RAH
You mean that you suggesting to use the tool and not to suggest to write script for it?
-
You mean that you suggesting to use the tool and not to suggest to write script for it?
Sorry I was not very clear, I use the tool and the generated script. IF it fails (it automatically rolls back the transaction) I then use a subset of the objects, starting with the tables. I repeat this till I get a successful merge. I then save that script and get the next subset, repeating until I get all the changes across to the target database. At that point I have a set of scripts that can be run sequentially to successfully update the target database. This is a pain but it is better than hand coding which is how I used to do it!
Never underestimate the power of human stupidity RAH