I seem to get this when tyring to use your script. Please advise.

Server: Msg 195, Level 15, State 10, Line 35

'SCHEMA_NAME' is not a recognized function name.

Same error as Anon

I keeping having issues with the column size of the audit table always being set to 1 - Any ideas?

Are you using server 2005?

Same - No SCHEMA_NAME Function

I created an SProc based on the above script that handles INSERT, UPDATE and DELETE. The audit table will have a complete history of each record, including the current record in the primary/trigger table.

I use an 'UpdateAction' field in the Audit table to capture the action.

I would highly recommend to anybody using this script to include a CreateDate and CreateUser field in the primary/trigger table. That will allow you to capture who made what changes.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER PROCEDURE dbo.spCreateAuditTable

        -- Add the parameters for the stored procedure here
        @TableName nvarchar(100)

AS

BEGIN

        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;


    --DECLARE @TABLENAME varchar(100)
        DECLARE @SCHEMA varchar(100)
        --SET @TABLENAME = N'Company'
        SET @SCHEMA = N'dbo'


        DECLARE @Done bit 
        SET @Done=0 


        DECLARE @CRLF char(2)
        SET @CRLF = char(10)
        DECLARE @SQL varchar(2000)
        SET @SQL = 'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''['+@SCHEMA+'].['+@TABLENAME+'_Audit]'') AND type in (N''U''))
        DROP TABLE [dbo].['+@TABLENAME+'_Audit]

CREATE TABLE ['@SCHEMA'].['@TABLENAME'_Audit] ('+@CRLF

        DECLARE @COLUMNID int
        SET @COLUMNID = 0
        DECLARE @COLUMNNAME varchar(1000)
        DECLARE @COLUMNTYPE varchar(100)
        DECLARE @COLUMNSIZE int


        WHILE @Done=0   
        BEGIN            
                SELECT top 1
                @COLUMNID=clmns.column_id,
                @COLUMNNAME=clmns.name ,
                @COLUMNTYPE=usrt.name ,
                @COLUMNSIZE=CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND 
        clmns.max_length <> -1 THEN clmns.max_length/2 ELSE clmns.max_length END AS int)
                FROM
                sys.tables AS tbl
                INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
                LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id
                LEFT OUTER JOIN sys.types AS baset ON baset.user_type_id = clmns.system_type_id and 
        baset.user_type_id = baset.system_type_id
                WHERE
                (tbl.name=@TABLENAME and SCHEMA_NAME(tbl.schema_id)=@SCHEMA)
                and clmns.column_id > @COLUMNID
                ORDER BY
                clmns.column_id asc


                IF @@rowcount=0  
                        begin   
                                SET @Done=1  
                        end
                else
                        begin
                                SET @SQL=@SQL+'        ['+@COLUMNNAME+'] ['+@COLUMNTYPE+'] '
                                IF (@COLUMNTYPE='nchar' OR @COLUMNTYPE='nvarchar' OR @COLUMNTYPE='varchar') SET @SQL=@SQL+'('+ltrim(str(@COLUMNSIZE))
        +') '
                                SET @SQL=@SQL+'NULL, '+@CRLF
                        end
        END


        SET @SQL=@SQL+'        ['+@TABLENAME+'UpdateDate] datetime,
        ['+@TABLENAME+'UpdateAction] nvarchar(10) )

'

        --print @SQL
        exec (@SQL)


        SET @SQL='IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].['+@TABLENAME+'_Trigger_Update]''))
        DROP TRIGGER [dbo].['+@TABLENAME+'_Trigger_Update]

IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N[dbo].['@TABLENAME'TriggerDelete]))

        DROP TRIGGER [dbo].['+@TABLENAME+'_Trigger_Delete]

IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N[dbo].['@TABLENAME'TriggerInsert]))

        DROP TRIGGER [dbo].['+@TABLENAME+'_Trigger_Insert]

'

        --print @SQL
        exec(@SQL)


        SET @SQL='CREATE TRIGGER ['+@TABLENAME+'_Trigger_Update] 
   ON  ['+@SCHEMA+'].['+@TABLENAME+'] 
   AFTER UPDATE

AS

BEGIN

        SET NOCOUNT ON;
        INSERT INTO '+@TABLENAME+'_Audit 
        SELECT *,getdate(),''Update'' FROM inserted

END

'

        exec (@SQL)
        --print @SQL


        SET @SQL='CREATE TRIGGER ['+@TABLENAME+'_Trigger_Delete] 
   ON  ['+@SCHEMA+'].['+@TABLENAME+'] 
   AFTER DELETE

AS

BEGIN

        SET NOCOUNT ON;
        INSERT INTO '+@TABLENAME+'_Audit 
        SELECT *,getdate(),''Delete'' FROM deleted

END

'

        exec (@SQL)
        --print @SQL


        SET @SQL='CREATE TRIGGER ['+@TABLENAME+'_Trigger_Insert] 
   ON  ['+@SCHEMA+'].['+@TABLENAME+'] 
   AFTER INSERT

AS

BEGIN

        SET NOCOUNT ON;
        INSERT INTO '+@TABLENAME+'_Audit 
        SELECT *,getdate(),''Insert'' FROM inserted

END

'

        exec (@SQL)
        --print @SQL

END

Msg 105, Level 15, State 1, Line 30

Unclosed quotation mark after the character string 'con'.

Msg 102, Level 15, State 1, Line 30

Incorrect syntax near 'con'.

This is the error i'm getting