/*
Script created by Henning Frettem, aka Lumbago @ SQLTeam, 2010-03-23
BE CAREFUL, THIS SCRIPT WILL MAKE CHANGES TO YOUR TABLES!!
*/

SET NOCOUNT ON

DECLARE   
  @TableName varchar(100) = 'SampleOrg',
  @AuditTableName varchar(200)

SET @AuditTableName = @TableName + '_audit'

DECLARE @SQL nvarchar(max) = ''

--> Add audit columns to the production table if they don't exist already
IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'DataVersion')
  SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD DataVersion int NOT NULL DEFAULT 1' + CHAR(13)

IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'CreatedDate')
  SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD CreatedDate datetime NOT NULL DEFAULT GETDATE()' + CHAR(13)

IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'CreatedBy')
  SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD CreatedBy nvarchar(200) NOT NULL DEFAULT SUSER_NAME()' + CHAR(13)
  
IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'ModifiedDate')
  SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD ModifiedDate datetime NOT NULL DEFAULT GETDATE()' + CHAR(13)

IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Tablename AND COLUMN_NAME = 'ModifiedBy')
  SET @SQL = @SQL + 'ALTER TABLE ' + @TableName + ' ADD ModifiedBy nvarchar(200) NOT NULL DEFAULT SUSER_NAME()' + CHAR(13)
  
IF LEN(@SQL) > 0
  BEGIN
    PRINT @SQL
    EXEC sp_executesql @SQL
    SET @SQL = ''
  END
  
--> Create the audit table with the same definition as the production table, but add the IsDeleted column
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @AuditTableName)
  BEGIN
    DECLARE @AuditTableDefinition table (
      RowNumber int IDENTITY(1, 1),
      DefinitionRow varchar(max)
      )
      
    INSERT INTO @AuditTableDefinition
    EXEC sp_ScriptTableForAudit @Tablename = @TableName, @NewTablename = @AuditTableName

    --> Need IsDeleted column in audit table    
    UPDATE @AuditTableDefinition 
      SET DefinitionRow = ', IsDeleted bit NOT NULL DEFAULT(0) )'
    WHERE RowNumber = (SELECT MAX(RowNumber) FROM @AuditTableDefinition)
    
    SELECT @SQL = @SQL + DefinitionRow FROM @AuditTableDefinition

    --> Run the create table script in the database 
    PRINT @SQL
    EXEC sp_executesql @SQL
    SET @SQL = ''
  END

--> Create Update trigger
IF NOT EXISTS (SELECT 1 FROM sys.triggers WHERE name = 'trg' + REPLACE(@TableName, '.', '') + 'Update')
  BEGIN
    SET @SQL = @SQL + '
      CREATE TRIGGER [trg' + REPLACE(@TableName, '.', '') + 'Update] ON ' + @TableName + '
      FOR UPDATE
      AS 

      SET NOCOUNT ON

      UPDATE a SET 
        a.DataVersion = b.DataVersion + 1,
        a.ModifiedDate = GETDATE(),
        a.ModifiedBy = SYSTEM_USER
      FROM ' + @TableName + ' a
        INNER JOIN inserted b
          ON '
          
    SELECT @SQL = @SQL + 'a.' + c.name + ' = b.' + c.name + ' AND '
    FROM sys.index_columns sc
      JOIN sys.columns c 
        ON sc.object_id = c.object_id 
          AND sc.column_id = c.column_id
      JOIN sys.indexes i
        ON sc.object_id = i.object_id 
          AND sc.index_id = i.index_id
    WHERE OBJECT_NAME(sc.object_id) = @TableName
      AND i.is_primary_key = 1
    ORDER BY index_column_id ASC

    SET @SQL = LEFT(@SQL, LEN(@SQL) - 4) + CHAR(13) + CHAR(13)

    SET @SQL = @SQL + '  INSERT INTO ' + @AuditTableName + '
        ('
      
    SELECT @SQL = @SQL + '[' + COLUMN_NAME + '], '
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @AuditTableName

    SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)
    SET @SQL = @SQL + ')
      SELECT '

    SELECT @SQL = @SQL + '[' + COLUMN_NAME + '], '
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @AuditTableName

    SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)
    SET @SQL = @SQL + ' = 0
      FROM deleted
      
      GO
      '
      
    PRINT @SQL
    EXEC sp_executesql @SQL
    SET @SQL = ''
  END

--> Create delete trigger
IF NOT EXISTS (SELECT 1 FROM sys.triggers WHERE name = 'trg' + REPLACE(@TableName, '.', '') + 'Delete')
  BEGIN
    SET @SQL = @SQL + '
      CREATE TRIGGER [trg' + REPLACE(@TableName, '.', '') + 'Delete] ON ' + @TableName + '
      FOR DELETE
      AS 

      SET NOCOUNT ON

      INSERT INTO ' + @AuditTableName + '
        ('
      
    SELECT @SQL = @SQL + '[' + COLUMN_NAME + '], '
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @AuditTableName

    SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)
    SET @SQL = @SQL + ')
      SELECT '

    SELECT @SQL = @SQL + '[' + COLUMN_NAME + '], '
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @AuditTableName

    SET @SQL = LEFT(@SQL, LEN(@SQL) - 1)
    SET @SQL = @SQL + ' = 1
      FROM deleted
      
      GO'

    PRINT @SQL
    EXEC sp_executesql @SQL
    SET @SQL = ''
  END

CREATE PROCEDURE sp_ScriptTableForAudit
--> Slightly altered version of http://blogs.techrepublic.com.com/datacenter/?p=431
 (
     @TableName SYSNAME,
     @IncludeFKConstraints BIT = 0,
     @IncludePKConstraints BIT = 1,
     @IncludeConstraints BIT = 0,
     @IncludeIndexes BIT = 0,
     @NewTableName SYSNAME,
     @UseSystemDataTypes BIT = 0
 )
 AS
 BEGIN


     DECLARE @MainDefinition TABLE
     (
         FieldValue VARCHAR(200)
     )

     DECLARE @DBName SYSNAME
     DECLARE @ClusteredPK BIT
     DECLARE @TableSchema NVARCHAR(255)
     SET @DBName = DB_NAME(DB_ID())

     SELECT @TableName = name FROM sysobjects WHERE id = OBJECT_ID(@TableName)

     DECLARE @ShowFields TABLE
     (
         FieldID INT IDENTITY(1,1),
         DatabaseName VARCHAR(100),
         TableOwner VARCHAR(100),
         TableName VARCHAR(100),
         FieldName VARCHAR(100),
         ColumnPosition INT,
         ColumnDefaultValue VARCHAR(100),
         ColumnDefaultName VARCHAR(100),
         IsNullable BIT,
         DataType VARCHAR(100),
         MaxLength INT,
         NumericPrecision INT,
         NumericScale INT,
         DomainName VARCHAR(100),
         FieldListingName VARCHAR(110),
         FieldDefinition CHAR(1),
         IdentityColumn BIT,
         IdentitySeed INT,
         IdentityIncrement INT,
         IsCharColumn BIT
     )

     DECLARE @HoldingArea TABLE
     (
         FldID SMALLINT IDENTITY(1,1),
         Flds VARCHAR(4000),
         FldValue CHAR(1) DEFAULT(0)
     )

     DECLARE @PKObjectID TABLE
     (
         ObjectID INT
     )

     DECLARE @Uniques TABLE
     (
         ObjectID INT
     )

     DECLARE @HoldingAreaValues TABLE
     (
         FldID SMALLINT IDENTITY(1,1),
         Flds VARCHAR(4000),
         FldValue CHAR(1) DEFAULT(0)
     )

     DECLARE @Definition TABLE
     (
         DefinitionID SMALLINT IDENTITY(1,1),
         FieldValue VARCHAR(200)
     )


    INSERT INTO @ShowFields
     (       
         DatabaseName,
         TableOwner,
         TableName,
         FieldName,
         ColumnPosition,
         ColumnDefaultValue,
         ColumnDefaultName,
         IsNullable,
         DataType,
         MaxLength,
         NumericPrecision,
         NumericScale,
         DomainName,
         FieldListingName,
         FieldDefinition,
         IdentityColumn,
         IdentitySeed,
         IdentityIncrement,
         IsCharColumn
     )

     SELECT
         DB_NAME(),
         TABLE_SCHEMA,
         TABLE_NAME,
         COLUMN_NAME,
         CAST(ORDINAL_POSITION AS INT),
         COLUMN_DEFAULT,
         dobj.name AS ColumnDefaultName,
         CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END,
         DATA_TYPE,
         CAST(CHARACTER_MAXIMUM_LENGTH AS INT),
         CAST(NUMERIC_PRECISION AS INT),
         CAST(NUMERIC_SCALE AS INT),
         DOMAIN_NAME,
         COLUMN_NAME + ',',
         '' AS FieldDefinition,
         CASE WHEN ic.object_id IS NULL THEN 0 ELSE 1 END AS IdentityColumn,
         CAST(ISNULL(ic.seed_value,0) AS INT) AS IdentitySeed,
         CAST(ISNULL(ic.increment_value,0) AS INT) AS IdentityIncrement,
         CASE WHEN st.collation_name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn
     FROM
         INFORMATION_SCHEMA.COLUMNS c
         JOIN sys.columns sc ON  c.TABLE_NAME = OBJECT_NAME(sc.object_id) AND c.COLUMN_NAME = sc.Name
         LEFT JOIN sys.identity_columns ic ON c.TABLE_NAME = OBJECT_NAME(ic.object_id) AND c.COLUMN_NAME = ic.Name
         JOIN sys.types st ON COALESCE(c.DOMAIN_NAME,c.DATA_TYPE) = st.name
         LEFT OUTER JOIN sys.objects dobj ON dobj.object_id = sc.default_object_id AND dobj.type = 'D'


     WHERE c.TABLE_NAME = @TableName
     ORDER BY
         c.TABLE_NAME, c.ORDINAL_POSITION

     SELECT TOP 1 @TableSchema = TableOwner
     FROM @ShowFields

     INSERT INTO @HoldingArea (Flds) VALUES('(')

     INSERT INTO @Definition(FieldValue)
     VALUES('CREATE TABLE ' + CASE WHEN @NewTableName IS NOT NULL THEN @NewTableName ELSE @DBName + '.' + @TableSchema + '.' + @TableName END)

     INSERT INTO @Definition(FieldValue)
     VALUES('(')

     INSERT INTO @Definition(FieldValue)
         SELECT
                 CHAR(10) + FieldName + ' ' +
                                     CASE
                                         WHEN DomainName IS NOT NULL AND @UseSystemDataTypes = 0 THEN DomainName + CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END
                                         ELSE UPPER(DataType) +
                                             CASE WHEN IsCharColumn = 1 THEN '(' + CAST(MaxLength AS VARCHAR(10)) + ')' ELSE '' END +
                                             CASE WHEN IdentityColumn = 1 THEN '' ELSE '' END + -->' IDENTITY(' + CAST(IdentitySeed AS VARCHAR(5))+ ',' + CAST(IdentityIncrement AS VARCHAR(5)) + ')' ELSE '' END +
                                             CASE WHEN IsNullable = 1 THEN ' NULL ' ELSE ' NOT NULL ' END +
                                             CASE WHEN ColumnDefaultName IS NOT NULL AND @IncludeConstraints = 1 THEN 'CONSTRAINT [' + ColumnDefaultName + '] DEFAULT' + UPPER(ColumnDefaultValue) ELSE '' END
                                     END + 

                                     CASE WHEN FieldID = (SELECT MAX(FieldID) FROM @ShowFields) THEN '' ELSE ',' END
         FROM    @ShowFields 

         IF @IncludeFKConstraints = 1
         BEGIN
             INSERT INTO @Definition(FieldValue)
             SELECT
               ',CONSTRAINT [' + name + '] FOREIGN KEY (' + ParentColumns + ') REFERENCES [' + ReferencedObject + '](' + ReferencedColumns + ')'
             FROM
             (
                 SELECT
                 ReferencedObject = OBJECT_NAME(fk.referenced_object_id), ParentObject = OBJECT_NAME(parent_object_id),fk.name,
                 REVERSE(SUBSTRING(REVERSE((
                 SELECT cp.name + ','
                 FROM
                 sys.foreign_key_columns fkc
                 JOIN sys.columns cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id
                 WHERE fkc.constraint_object_id = fk.object_id
                 FOR XML PATH('')
                 )), 2, 8000)) ParentColumns,
                 REVERSE(SUBSTRING(REVERSE((
                 SELECT cr.name + ','
                 FROM
                 sys.foreign_key_columns fkc
                 JOIN sys.columns cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
                 WHERE fkc.constraint_object_id = fk.object_id
                 FOR XML PATH('')
                 )), 2, 8000)) ReferencedColumns
                 FROM sys.foreign_keys fk
             ) a
             WHERE ParentObject = @TableName
           END
         IF @IncludeConstraints = 1
           BEGIN
             INSERT INTO @Definition(FieldValue)
             SELECT',CONSTRAINT [' + name + '] CHECK ' + definition FROM sys.check_constraints
             WHERE OBJECT_NAME(parent_object_id) = @TableName
           END

         IF @IncludePKConstraints = 1
           BEGIN
             INSERT INTO @PKObjectID(ObjectID)
             SELECT DISTINCT
                 PKObject = cco.object_id
             FROM
                 sys.key_constraints cco
                 JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
                 JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
             WHERE
                 OBJECT_NAME(parent_object_id) = @TableName    AND   
                 i.type = 1 AND
                 is_primary_key = 1

         /*
         INSERT INTO @Uniques(ObjectID)
         SELECT DISTINCT
             PKObject = cco.object_id
         FROM
             sys.key_constraints cco
             JOIN sys.index_columns cc ON cco.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id
             JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
         WHERE
             OBJECT_NAME(parent_object_id) = @TableName AND       
             i.type = 2 AND
             is_primary_key = 0 AND
             is_unique_constraint = 1 
             
         */
           SET @ClusteredPK = CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE 0 END

           INSERT INTO @Definition(FieldValue)
           SELECT    ',CONSTRAINT ' + CASE type 
              WHEN 'PK' 
                THEN 'PK_' + @NewTableName + ' PRIMARY KEY ' + 
                  CASE WHEN pk.ObjectID IS NULL THEN ' NONCLUSTERED ' ELSE ' CLUSTERED ' END
              WHEN 'UQ' THEN name + ' UNIQUE ' 
              END 
              + 
              CASE WHEN u.ObjectID IS NOT NULL THEN ' NONCLUSTERED ' ELSE '' END + '(' +
           REVERSE(SUBSTRING(REVERSE((
               SELECT
                   c.name +  + CASE WHEN cc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','
               FROM
                   sys.key_constraints ccok
                   LEFT JOIN sys.index_columns cc ON ccok.parent_object_id = cc.object_id AND cco.unique_index_id = cc.index_id


                   LEFT JOIN sys.columns c ON cc.object_id = c.object_id AND cc.column_id = c.column_id
                   LEFT JOIN sys.indexes i ON cc.object_id = i.object_id AND cc.index_id = i.index_id
               WHERE
                   i.object_id = ccok.parent_object_id AND
                   ccok.object_id = cco.object_id
               FOR XML PATH('')
           )), 2, 8000)) + ', DataVersion ASC)'
           FROM
               sys.key_constraints cco
               LEFT JOIN @PKObjectID pk ON cco.object_id = pk.ObjectID
               LEFT JOIN @Uniques u ON cco.object_id = u.objectID
           WHERE
               OBJECT_NAME(cco.parent_object_id) = @TableName
         END

         INSERT INTO @Definition(FieldValue)
         VALUES(')')

         IF @IncludeIndexes = 1
         BEGIN
             INSERT INTO @Definition(FieldValue)
             SELECT
                 'CREATE ' + type_desc + ' INDEX [' + [name] COLLATE SQL_Latin1_General_CP1_CI_AS + '] ON [' +  OBJECT_NAME(object_id) + '] (' +
                 REVERSE(SUBSTRING(REVERSE((
                     SELECT name + CASE WHEN sc.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ','
                     FROM
                         sys.index_columns sc
                         JOIN sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id
                     WHERE
                         OBJECT_NAME(sc.object_id) = @TableName AND
                         sc.object_id = i.object_id AND
                         sc.index_id = i.index_id
                     ORDER BY index_column_id ASC
                     FOR XML PATH('')
             )), 2, 8000)) + ')'
             FROM sys.indexes i
             WHERE
                 OBJECT_NAME(object_id) = @TableName


                AND CASE WHEN @ClusteredPK = 1 AND is_primary_key = 1 AND type = 1 THEN 0 ELSE 1 END = 1
                 AND is_unique_constraint = 0
                 AND is_primary_key = 0
         END
             INSERT INTO @MainDefinition(FieldValue)
             SELECT FieldValue FROM @Definition
             ORDER BY DefinitionID ASC

         SELECT * FROM @MainDefinition
 END
 GO