Toad World® Forums

Bulk add audit columns as part of DDL script generation

Hi,
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
    to
    col1, col2, col3, Created, LastUpdated

whereas I would like it to change from:
col1, Created, LastUpdated, col2
to
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!

Hi,

I uploaded new macro that sets order of attributes Created and LastUpdated to second and third position in a table (there is simple restriction, number of attributes must be higher than two, otherwise no reordering will happen in such entity).

http://modeling.inside.quest.com/entry!default.jspa?categoryID=34&externalID=4897

Note: the macro will work in the latest BETA version only!

In addition, you might find useful the following tip: Create the two attributes in one entity. Then navigate to the entity in Physical Model Explorer, right click the Created column and choose Macros | Copy Attribute to All Entities. Then click the Macros menu in toolbar and choose item Change Order of Predefined Attributes. It can’t be faster :slight_smile:

Autogenerate the columns is not recommended. The above mentioned approach should work fine, I guess.

How to install custom packages:
http://blogs.inside.quest.com/modeling/2010/10/21/how-to-import-custom-packages/

Regards,

Vaclav

Message was edited by: vaclav

Fantastic! Thanks very much!

You are welcome :slight_smile: