Add "DateModified" Column with an Update Trigger To All Tables that contain a "DateCreated" Column

Question

I have many tables that have a [DateCreated] column that also needs a [DateModified] column.

The [DateModified] column will need an Update trigger that inserts the current date (getdate()) into this new [DateModified] column.

Each table uses custom schema, not [dbo].

Whats the easiest/best way to do this?

Answer 1

This will get you the alter table statements, which you can cut/paste into a new query window to execute. I'll leave it as an exercise for the reader to use this same technique to generate the create trigger statements.

select 'alter table ' + quotename(s.name) + '.' + quotename(t.name) + ' add [DateModified] datetime'
    from sys.columns c
        inner join sys.tables t
            on c.object_id = t.object_id
        inner join sys.schemas s
            on t.schema_id = s.schema_id
        left join sys.columns c2
            on t.object_id = c2.object_id
                and c2.name = 'DateModified'
    where c.name = 'DateCreated'
        and t.type = 'U'
        and c2.column_id is null /* DateModified column does not already exist */