Add Audit Fields to Every Table - SQL Server

Adding the Fields

When executed against the database of interest, the following T-SQL code will generate a set of SQL statements which, when executed against the same database, will add audit fields to every database table. Audit fields track the username and timestamp for the creation and updating of records in the database.

{copytext|code}
declare 
     @UserDataType      varchar(20)
    ,@DefaultUser       int
    ,@CreatedByField    varchar(50)
    ,@CreatedOnField    varchar(50)
    ,@UpdatedByField    varchar(50)
    ,@UpdatedOnField    varchar(50)
select
     @UserDataType      = 'int'
    ,@DefaultUser       = 1
    ,@CreatedByField    = 'CreatedBy'
    ,@CreatedOnField    = 'CreatedOn'
    ,@UpdatedByField    = 'UpdatedBy'
    ,@UpdatedOnField    = 'UpdatedOn'

;with MyTables as (
    select
        name
    from
        sys.objects
    where 1=1
        and type = 'U'
        and name not like 'aspnet[_]%'
        and name not in ('MenuItem','MenuItemRole')
    )
/*- Comment Header ------------------------------------------------------------------------------*/
select
     name
    ,sequence = 0
    ,sql = '/*- ' + name + ' ' + replicate('-',92-len(name)) + '*/'
from
    MyTables
where 
    Name not in (select o.name from sys.objects o inner join sys.columns c 
                on o.object_id = c.object_id
        where o.type = 'U' and c.name=@CreatedByField)
    or
    Name not in (select o.name from sys.objects o inner join sys.columns c 
                on o.object_id = c.object_id
        where o.type = 'U' and c.name=@CreatedOnField)
    or
    Name not in (select o.name from sys.objects o inner join sys.columns c 
                on o.object_id = c.object_id
        where o.type = 'U' and c.name=@UpdatedByField)
    or
    Name not in (select o.name from sys.objects o inner join sys.columns c 
                on o.object_id = c.object_id
        where o.type = 'U' and c.name=@UpdatedOnField)

/*- CreatedBy, Part 1 (Add nullable column) -----------------------------------------------------*/
union select
     name
    ,sequence = 10
    ,sql = 'ALTER TABLE [' + name + '] ADD ' + @CreatedByField + ' ' + 
                @UserDataType + ' null'
from 
    MyTables
where 1=1
    and Name not in (select o.name from sys.objects o inner join sys.columns c 
                on o.object_id = c.object_id
        where o.type = 'U' and c.name=@CreatedByField)

/*- CreatedBy, Part 2 (GO) ----------------------------------------------------------------------*/
union select
     name
    ,sequence = 32
    ,sql = 'GO'
from 
    MyTables
where 1=1
    and Name not in (select o.name from sys.objects o inner join sys.columns c 
                on o.object_id = c.object_id
        where o.type = 'U' and c.name=@CreatedByField)

/*- CreatedBy, Part 3 (Update column with default user) -----------------------------------------*/
union select
     name
    ,sequence = 33
    ,sql = 'UPDATE [' + name + '] SET ' + @CreatedByField + ' = ' + convert(varchar(255), @DefaultUser)
from 
    MyTables
where 1=1
    and Name not in (select o.name from sys.objects o inner join sys.columns c 
                on o.object_id = c.object_id
        where o.type = 'U' and c.name=@CreatedByField)

/*- CreatedBy, Part 4 (Alter column to be non-nullable) -----------------------------------------*/
union select
     name
    ,sequence = 34
    ,sql = 'ALTER TABLE [' + name + '] ALTER COLUMN ' + @CreatedByField + ' ' 
                + @UserDataType + ' not null'
from 
    MyTables
where 1=1
    and Name not in (select o.name from sys.objects o inner join sys.columns c 
                on o.object_id = c.object_id
        where o.type = 'U' and c.name=@CreatedByField)

/*- CreatedOn -----------------------------------------------------------------------------------*/
union select
     name
    ,sequence = 20
    ,sql = 'ALTER TABLE [' + name + '] ADD ' + @CreatedOnField + 
                ' datetime not null default getdate()'
from 
    MyTables
where 1=1
    and Name not in (select o.name from sys.objects o inner join sys.columns c 
                on o.object_id = c.object_id
        where o.type = 'U' and c.name=@CreatedOnField)

/*- UpdatedBy, Part 1 ---------------------------------------------------------------------------*/
union select
     name
    ,sequence = 31
    ,sql = 'ALTER TABLE [' + name + '] ADD ' + @UpdatedByField + ' ' + 
                @UserDataType + ' null'
from 
    MyTables
where 1=1
    and Name not in (select o.name from sys.objects o inner join sys.columns c 
                on o.object_id = c.object_id
        where o.type = 'U' and c.name=@UpdatedByField)

/*- UpdatedBy, Part 2 ---------------------------------------------------------------------------*/
union select
     name
    ,sequence = 32
    ,sql = 'GO'
from 
    MyTables
where 1=1
    and Name not in (select o.name from sys.objects o inner join sys.columns c 
                on o.object_id = c.object_id
        where o.type = 'U' and c.name=@UpdatedByField)

/*- UpdatedBy, Part 3 ---------------------------------------------------------------------------*/
union select
     name
    ,sequence = 33
    ,sql = 'UPDATE [' + name + '] SET ' + @UpdatedByField + ' = ' + convert(varchar(255), @DefaultUser)
from 
    MyTables
where 1=1
    and Name not in (select o.name from sys.objects o inner join sys.columns c 
                on o.object_id = c.object_id
        where o.type = 'U' and c.name=@UpdatedByField)

/*- UpdatedBy, Part 4 ---------------------------------------------------------------------------*/
union select
     name
    ,sequence = 34
    ,sql = 'ALTER TABLE [' + name + '] ALTER COLUMN ' + @UpdatedByField + ' ' 
                + @UserDataType + ' not null'
from 
    MyTables
where 1=1
    and Name not in (select o.name from sys.objects o inner join sys.columns c 
                on o.object_id = c.object_id
        where o.type = 'U' and c.name=@UpdatedByField)

/*- UpdatedOn -----------------------------------------------------------------------------------*/
union select
     name
    ,sequence = 40
    ,sql = 'ALTER TABLE [' + name + '] ADD ' + @UpdatedOnField + 
                ' datetime not null default getdate()'
from 
    MyTables
where 1=1
    and Name not in (select o.name from sys.objects o inner join sys.columns c 
                on o.object_id = c.object_id
        where o.type = 'U' and c.name=@UpdatedOnField)
/*-----------------------------------------------------------------------------------------------*/
order by 
     name
    ,sequence

Adding Foreign Keys to the User Table

When executed against the database of interest, the following T-SQL code will generate a set of SQL statements which, when executed against the same database, will add foreign keys on the CreatedBy and UpdatedBy fields against your user table.

{copytext|foreignKeys}
declare 
     @UserDataType      varchar(20)
    ,@DefaultUser       int
    ,@CreatedByField    varchar(50)
    ,@CreatedOnField    varchar(50)
    ,@UpdatedByField    varchar(50)
    ,@UpdatedOnField    varchar(50)
    ,@UserTable         varchar(50)
    ,@PrimaryKeyField   varchar(50)
    
select
     @UserDataType      = 'int'
    ,@DefaultUser       = 1
    ,@CreatedByField    = 'CreatedBy'   -- NO BRACKETS, SPACES, 
    ,@UpdatedByField    = 'UpdatedBy'   -- OR PERIODS!
    ,@UserTable         = 'AppUser'     -- ARE ALLOWED IN 
    ,@PrimaryKeyField   = 'AppUserID'   -- ANY OF THESE

;with MyTablesA as (
    select
         name
        ,FkeyCreated = 'FK_' + name + '_' + @UserTable + '_' + @CreatedByField
        ,FkeyUpdated = 'FK_' + name + '_' + @UserTable + '_' + @UpdatedByField
    from
        sys.objects
    where 1=1
        and type = 'U'
        and name not like 'aspnet[_]%'
        and Name in (
            select 
                o.name 
            from 
                sys.objects o 
                inner join sys.columns c 
                    on o.object_id = c.object_id
            where 1=1
                and o.type = 'U' 
                and c.name in (@CreatedByField, @UpdatedByField)
            )
    )
/*-----------------------------------------------------------------------------------------------*/
,MyTables as (
    select * from MyTablesA
    where
        FkeyCreated not in (select name from sys.objects)
        or
        FkeyUpdated not in (select name from sys.objects)        
    )      
/*- Comment Header ------------------------------------------------------------------------------*/
select
     name
    ,sequence = 0
    ,sql = '/*- ' + name + ' ' + replicate('-',92-len(name)) + '*/'
from
    MyTables

/*- CreatedBy -----------------------------------------------------------------------------------*/
union select
     name
    ,sequence = 10
    ,sql = 'ALTER TABLE [' + name + '] with check add constraint [' + FkeyCreated
            + '] foreign key (' + @CreatedByField + ') references ' 
            + @UserTable + ' (' + @PrimaryKeyField + ')'
from 
    MyTables
where 1=1
    and Name in (select o.name from sys.objects o inner join sys.columns c 
                on o.object_id = c.object_id
        where o.type = 'U' and c.name=@CreatedByField)
    and FkeyCreated not in (select name from sys.objects)       

/*- UpdatedBy -----------------------------------------------------------------------------------*/
union select
     name
    ,sequence = 20
    ,sql = 'ALTER TABLE [' + name + '] with check add constraint [' + FkeyUpdated
            + '] foreign key (' + @UpdatedByField + ') references ' 
            + @UserTable + ' (' + @PrimaryKeyField + ')'
from 
    MyTables
where 1=1
    and Name in (select o.name from sys.objects o inner join sys.columns c 
                on o.object_id = c.object_id
        where o.type = 'U' and c.name=@UpdatedByField)  
    and FkeyUpdated not in (select name from sys.objects)