Audit Wizard Script (SQL server 2008)

Este es el primer post donde desarrollo lo que realmente puede llegar a interesar, en este caso es un simple hacedor de script que genera tablas de auditoria con su respectivo disparador, como siempre digo, todo se puede mejorar pero partiendo de una primera idea se puede mejorar aun mas, Cualquier consulta o idea no existe problema en compartirla y discutirla.

El siguiente script es para educación solamente, no es responsable de cualquier daño o mal uso que el código o parte de el pueda producir.

Saludos cordiales

This is the first post where I develop what can really get interested, here is a simple script that generates a wizard audit tables with its own release, as I always say, everything can be improved but starting from an initial idea can be improve further, Any question or idea to share it no problem and discuss it.

The following script is for education only, is not responsible for any damage or misuse of the code or part of it can produce

Best regards

-- =============================================
-- Author:  Skynet Silverstar
-- Create date:
-- Description: Script maker where you conform
-- audit info on a specific SQL table
-- =============================================
-- Creador de script donde conforma
--    Informacion de auditoria sobre
-- una tabla especifica
-- =============================================


DECLARE @object_name VARCHAR(50)
DECLARE @column_name VARCHAR(50)
DECLARE @is_nullable VARCHAR(50)
DECLARE @type   VARCHAR(50)
DECLARE @max_length  INT
DECLARE @precision  INT
DECLARE @scale   INT
DECLARE @Audit_name  VARCHAR(255)
DECLARE @trigger_name VARCHAR(255)


-- The target table name
-- El nombre de la tabla
SET @object_name = 'factura'




--Here change the name of tables and triggers if you want
--Aqui cambie el nombre de las tablas y disparadore si usted quiere!
SET @Audit_name  = 'AUDIT_' + @object_name
SET @trigger_name = 'AUDIT_TR_' + @object_name


if exists( select * from sys.objects where name in ( @Audit_name, @trigger_name) )
BEGIN
 PRINT '---------------------------------------------------------'
 PRINT '--Informe de proceso con error ATENCION!'
 PRINT '---------------------------------------------------------'
 PRINT '--La tabla de auditoria ' + @Audit_name + ' ya existe, verificar y volver a ejecutar el proceso'
 PRINT '--El trigger ' + @trigger_name + ' ya existe, verificar y volver a ejecutar el proceso'
 PRINT '--'
 PRINT '--Sugerido hacer backup de la tabla de auditoria y base de datos'
 PRINT '--copiar el resultado y descomentar los comandos siguientes y ejecutar F5'
 PRINT '--DROP TABLE ' + @Audit_name
 PRINT '--DROP TRIGGER ' + @trigger_name
 PRINT ''
 PRINT '---------------------------------------------------------'
 PRINT '--Warning!! proccess info error
 PRINT '---------------------------------------------------------'
 PRINT '--The audit table ' + @Audit_name + ' already exists, Verify and run again the script'
 PRINT '--Trigger ' + @trigger_name + ' already exists, Verify and run again the script'
 PRINT '--'
 PRINT '--Make a backup and save the information'
 PRINT '--Execute the next lines if you want delete the old schema'
 PRINT '--DROP TABLE ' + @Audit_name
 PRINT '--DROP TRIGGER ' + @trigger_name


END ELSE BEGIN
 SET NOCOUNT ON



 PRINT '-------- Script de creacion de tabla de auditoria AUDIT_' + @object_name+ '--------'
 PRINT '-------- Make the script to make the audit table AUDIT_' + @object_name+ '--------'
 PRINT '-------- AUTOR: ' + USER_NAME()
 PRINT '-------- WORKPLACE: ' + HOST_NAME() + '/' + APP_NAME()

 PRINT 'SET ANSI_NULLS ON'
 PRINT 'GO'
 PRINT ''
 PRINT 'SET QUOTED_IDENTIFIER ON'
 PRINT 'GO'
 PRINT ''
 PRINT 'SET ANSI_PADDING ON'
 PRINT 'GO'
 PRINT ''
 PRINT 'CREATE TABLE ' + @Audit_name + ' ('
 PRINT CHAR(9) + ' [AUDIT_ID] ' + CHAR(9) + ' INT IDENTITY(1,1) ' + CHAR(9) + ','
 PRINT CHAR(9) + ' [AUDIT_ACTION] ' + CHAR(9) + ' CHAR(1) ' + CHAR(9) + ' ,'
 DECLARE Table_cursor CURSOR FOR
  SELECT
   o.name AS object_name,
   c.name AS column_name,
   is_nullable = CASE WHEN c.is_nullable <> 0 THEN 'NULL' ELSE 'NOT NULL' END,  
   [type] = '[' + upper(t.name) + '] ' + CASE
    WHEN t.name in ('nvarchar', 'nchar','char','varchar') THEN
    '(' + convert(varchar(4), c.max_length) + ')'
    WHEN t.name in ('float', 'numeric','money','smallmoney','real','decimal') THEN
    '(' + CONVERT(VARCHAR(4), c.precision) + ',' + CONVERT(VARCHAR(4), c.scale) + ')'
    ELSE ''

   END,
   c.max_length ,
   c.precision ,
   c.scale
  FROM
   sys.objects AS o
  INNER JOIN
   sys.columns AS c
  ON ( o.object_id = c.object_id)
  INNER JOIN
   systypes as t
  ON ( c.system_type_id = t.xtype )
  WHERE
   o.type = 'U'
   AND o.name = @object_name
  ORDER BY
   o.name ,
   column_id
 OPEN Table_cursor
 FETCH NEXT FROM Table_cursor
 INTO @object_name , @column_name ,@is_nullable, @type ,
   @max_length, @precision , @scale

 WHILE @@FETCH_STATUS = 0
 BEGIN

  PRINT CHAR(9) + ' [' + @column_name + '] ' + CHAR(9) + @TYPE + ' ' + CHAR(9) + @is_nullable + ','
 
  FETCH NEXT FROM Table_cursor
  INTO @object_name , @column_name ,@is_nullable, @type ,
    @max_length, @precision , @scale
 END
 CLOSE Table_cursor
 DEALLOCATE Table_cursor


 PRINT CHAR(9) + ' [AUDIT_DATE] ' + CHAR(9) + ' DATETIME ' + CHAR(9) + ' ,'
 PRINT CHAR(9) + ' [AUDIT_USER] ' + CHAR(9) + ' VARCHAR(50) ' + CHAR(9) + ' ,'
 PRINT CHAR(9) + ' [AUDIT_APLICATION] ' + CHAR(9) + ' VARCHAR(255) ' + CHAR(9) + ' ,'
 PRINT CHAR(9) + ' [AUDIT_HOST] ' + CHAR(9) + ' VARCHAR(50) ' + CHAR(9) + ' NOT NULL,'
 PRINT CHAR(9) + ' [AUDIT_TIMESTAMP] ' + CHAR(9) + ' TIMESTAMP ' + CHAR(9) + ' ,'

 PRINT ' CONSTRAINT [' + @Audit_name + '_PK] PRIMARY KEY CLUSTERED '
 PRINT ' ('
 PRINT '  [AUDIT_ID] ASC'
 PRINT ' )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'
 PRINT ' ) ON [PRIMARY]'
 PRINT ' '
 PRINT ' GO'
 PRINT ' '
 PRINT ' SET ANSI_PADDING OFF'
 PRINT ' GO'




 /********************************************************************************
  creacion del disparador
  Make a trigger
  
 ********************************************************************************/
 PRINT '-------- Script de creacion de triger de auditoria AUDIT_TR_' + @object_name+ '--------'
 PRINT '-------- Make a script of Audit trigger named AUDIT_TR_' + @object_name+ '--------'
 PRINT '-------- AUTOR: ' + USER_NAME()
 PRINT '-------- WORKPLACE: ' + HOST_NAME() + '/' + APP_NAME()

 PRINT 'SET ANSI_NULLS ON'
 PRINT 'GO'
 PRINT ''
 PRINT 'SET QUOTED_IDENTIFIER ON'
 PRINT 'GO'
 PRINT ''
 PRINT 'SET ANSI_PADDING ON'
 PRINT 'GO'
 PRINT ''
 PRINT 'CREATE TRIGGER ' + @trigger_name + ' '
 PRINT ' ON ' + @object_name + ' '
 PRINT ' AFTER INSERT, UPDATE, DELETE'
 PRINT ' AS'
 PRINT ' BEGIN'
 PRINT CHAR(9) + ' -- Verifico que tipo de accion se va a realizar'
 PRINT CHAR(9) + ' -- U : almaceno el dato a actualizar'
 PRINT CHAR(9) + ' -- D : almaceno el dato a ser eliminado'
 PRINT CHAR(9) + ' -- I : almaceno el dato a ser insertado'
 PRINT ''
 PRINT CHAR(9) + ' -- Verify the type of operatio to do'
 PRINT CHAR(9) + ' -- U : Save on update'
 PRINT CHAR(9) + ' -- D : Save on delete'
 PRINT CHAR(9) + ' -- I : save on insert'

 PRINT CHAR(9) + 'DECLARE @ACTION CHAR(1)'

 PRINT ''
 PRINT CHAR(9) + 'IF EXISTS(SELECT * FROM DELETED) AND EXISTS(SELECT * FROM INSERTED)'
 PRINT CHAR(9) + CHAR(9) + 'SET @ACTION = ''U'''
 PRINT CHAR(9) + 'ELSE IF EXISTS(SELECT * FROM DELETED) '
 PRINT CHAR(9) + CHAR(9) + 'SET @ACTION = ''D'' '
 PRINT CHAR(9) + 'ELSE IF EXISTS(SELECT * FROM INSERTED)'
 PRINT CHAR(9) + CHAR(9) + 'SET @ACTION = ''I'' '
 PRINT ''


 DECLARE @CAMPOS VARCHAR(8000)
 SET @CAMPOS = ''
 DECLARE Table_cursor CURSOR FOR
  SELECT
   c.name AS column_name
  FROM
   sys.objects AS o
  INNER JOIN
   sys.columns AS c
  ON ( o.object_id = c.object_id)
  WHERE
   o.type = 'U'
   AND o.name = @object_name
  ORDER BY
   o.name ,
   column_id
 OPEN Table_cursor
 FETCH NEXT FROM Table_cursor
 INTO @column_name

 WHILE @@FETCH_STATUS = 0
 BEGIN
  SET @CAMPOS = @CAMPOS + '' + ' [' + @column_name + '] ,'
 
  FETCH NEXT FROM Table_cursor
  INTO @column_name
 END
 CLOSE Table_cursor
 DEALLOCATE Table_cursor
 PRINT CHAR(9) + 'SET NOCOUNT ON'

 PRINT CHAR(9) + 'IF (@ACTION IN ( ''I'')) BEGIN'
 PRINT CHAR(9) + CHAR(9) + 'INSERT INTO ' + @Audit_name + ' '
 PRINT CHAR(9) + CHAR(9) + '( ' + @CAMPOS + '[AUDIT_ACTION], [AUDIT_DATE], [AUDIT_USER], [AUDIT_APLICATION],[AUDIT_HOST])'
 PRINT CHAR(9) + CHAR(9) + ' SELECT ' + @CAMPOS + '@ACTION, GetDate(), USER_NAME(), APP_NAME(), HOST_NAME() FROM INSERTED'
 PRINT CHAR(9) + 'END'

 PRINT CHAR(9) + 'ELSE IF (@ACTION IN (''U'', ''D'')) BEGIN'
 PRINT CHAR(9) + CHAR(9) + 'INSERT INTO ' + @Audit_name + ' '
 PRINT CHAR(9) + CHAR(9) + '( ' + @CAMPOS + '[AUDIT_ACTION], [AUDIT_DATE], [AUDIT_USER], [AUDIT_APLICATION],[AUDIT_HOST])'
 PRINT CHAR(9) + CHAR(9) + ' SELECT ' + @CAMPOS + '@ACTION, GetDate(), USER_NAME(), APP_NAME(), HOST_NAME() FROM DELETED'
 PRINT CHAR(9) + 'END'
 PRINT 'END'



 PRINT '---------------------------------------------------------'
 PRINT '--Informe de proceso'
 PRINT '---------------------------------------------------------'
 PRINT '--Se generò la tabla de auditoria ' + @Audit_name
 PRINT '--Se generò sobre tabla ' + @object_name + ' el trigger ' + @trigger_name + ' '
 PRINT '--'
 PRINT '--Copie el contenido de resultado a un archivo. sql'
 PRINT '--Nombre: xxx_creacion_auditoria_' + @object_name + '.sql'
 PRINT ''
 PRINT '---------------------------------------------------------'
 PRINT '--Proccess info'
 PRINT '---------------------------------------------------------'
 PRINT '--Made the audit table named ' + @Audit_name
 PRINT '--With the target table ' + @object_name + ' made the trigger ' + @trigger_name + ' '
 PRINT '--'
 PRINT '--Copy the result to a new query and save '
 PRINT '--Name: xxx_Create_Audit_' + @object_name + '.sql'
END
/*
Compatible SQL 2008
SQL 2008 compatible
*/