Automatically Generating Audit Columns for your Database - no monkey work!

Every database should really have audit columns in it - even if your users say they are not required. A simple way to get you up and running (schema-wise) is to use the following Microsoft SQL Server script to generate a text file to add your standard audit columns to every table.

Open a query on your database in SQL 2005 Management Studio. Change your output mode to "text", and then run it by pressing F5. The output window will have your script to run for all tables in your database - table auditing heaven!

SELECT 'ALTER TABLE '  + TABLE_NAME + ' ADD Modified datetime NOT NULL DEFAULT GETDATE(),
 ModifiedBy nvarchar(255) NOT NULL DEFAULT USER_NAME(),
Created datetime NOT NULL DEFAULT GETDATE(),
CreatedBy nvarchar(255) NOT NULL DEFAULT USER_NAME() ' FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME <> 'sysdiagrams'