SQL Audit Part 4: Altering the Tables - Audit Defined Fields

So, we have our tables created with an ID field, now we need to add to those tables the basic fields to store information about the audit of each record, such as action, date time, status etc.  Now, for ease of altering the set of required fields, I have placed the field names and specifications into another table.  When I need to add a new field, I can just add a new record to the table.


SET @sql = N'IF EXISTS(SELECT * FROM '+@databaseName+'.INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='''+@dbSchema+''' AND CONSTRAINT_NAME=''DF_'+@auditTableName+'_'+@FieldName+''' AND TABLE_NAME='''+@auditTableName+''')
BEGIN
alter table '+@databaseName+'.'+@dbSchema+'.'+@auditTableName+' drop constraint DF_'+@auditTableName+'_'+@FieldName+';
END';
SET @sql = 'EXEC '+cast(@databaseName as nvarchar(255))+'.sys.sp_executesql N'''+REPLACE(@sql,'''','''''')+'''';
EXEC sp_executesql @sql;


One of the issues I needed to address was adding constraints to the audit table in the source database.  To do that, I found out that the sp_executesql command actually was an object in each database, so to call the instance of the stored procedure in another database you can use <database>.sys.sp_executesql <nvarchar execute to>

Yes, I know that for some, this isn't earth shattering, but for me who was doing this to improve my SQL skills, it was a revelation.  Even helped me to re-factor my earlier code to the manner that it is now (more reliable and better performing).  I used the same thoughts on getting the table list to see if the table existed in the database.

So, the above code will remove a constraint on a table in a different database.  A very handy concept on cross database management.

Again, I will try to find somewhere to upload all of the code, but for now I am just pointing out the smart (in my opinion) aspects of each functionality.