I am modelling a data warehouse and have a standard set of audit columns (LastUpdated, Created etc) that I would like to add to every table. However I have certain requirements that make this quite tricky:
- I don’t want the columns in my Toad model so would like them added at the point that the ddl script is generated or after
- I want the columns added at a certain position within every table (e.g. as columns 2 and 3). This is because, when I add new columns to my model in the future, I do not want them to affect the column order when I re-generate the DDL script, as they would do if I then just appended my audit columns to the new version of the table, e.g. column order would change from
col1, col2, Created, LastUpdated
col1, col2, col3, Created, LastUpdated
whereas I would like it to change from:
col1, Created, LastUpdated, col2
col1, Created, LastUpdated, col2, col3
(i.e. no change of order)
It would be fine if I could just automate these changes in a t-sql script but, because I want to insert columns into the middle of the table rather than append columns, this is not really possible.
Am I just being too awkward! Any ideas/thoughts much appreciated!