Build Utility to Reorder SQL Table Columns via Migration Script

[AN-BC]

SQL Server does not natively support the ability to change column order for an existing table via an ALTER TABLE statement. As a result, our migrations, when new columns are added, end up having those columns all at the end of the table instead of wherever the developer intended them to be in the table's column order. Generally speaking this shouldn't affect functionality in any way as people aren't using column order to drive behavior. However, this is "messy" and it is important that we be able to control column order in our tables.

For this reason, we will introduce a new utility method within the CodeGen tool that is capable of generating just such a migration script. The idea is that:

1. Using SQL Server Management Studio (SSMS) a developer will order the columns in their table as desired - this can be done in the SSMS UI.
2. After this, the CodeGen tool will be run with a specific option where we will provide the table name(s) that we want column reordering scripts built for. There could be better ways to do this ranging from somehow auto-detecting changes to also doing this in another tool outside of CodeGen, but this seems like a logical place to do it, at least to start.
3. The CodeGen tool will generate a SQL script to a file in a location specified in the config.json file in a new section to be called "table_column_reordering". This new section will have the following properties:
   • table_names - list of table names to generate scripts for - can be a single table name or comma delimited. Each table name is assumed to be prefixed with a schema name so the string would look something like __mj.Entity, __mj.EntityField etc
   • output_directory a fully qualified directory path that resolves from the cwd that CodeGen runs from. For example we could provide a relative path to the migrations folder
   • file_prefix - a string to be prepended onto each file generated by the routine. If omitted the output files will be just column_reorder_tablename.sql where tablename is replaced with the actual table name. Developers will change these files to merge them into one, or into other migration scripts etc and rename those files as desired for migration script purposes.

@jordanfanapour the first draft of the code that does the heavy lifting on this is here: https://github.com/MemberJunction/MJ/pull/631

[AN-BC]

@jordanfanapour also, at the end of this set of generated SQL commands, we would want to call __mj.spUpdateExistingEntityFieldsFromSchema which will handle updating the metadata to reflect the sequence of columns in the fields in the table. No new fields will be added, this process just reorders the existing columns so this last SP call will ensure the metadata reflects the physical table order.

However, the really important thing that will be needed in addition to this is to also have all of the SPs and Views associated with the entity regenerated. They will be generated by the developer already who has created the new order, and the migration will need to include those new SPs and Views to be in the script, after all of the above is done. We can do this automatically in this script generation by pulling those SPs and Views from the Generated directory and dropping them at the end of this newly created file.