SQL Audit Part 3: Creating the Tables

The first thing I needed to do was to determine that the source table does indeed exist.  Now, assuming that @databaseName and @tableName are inputs to the stored procedure, look at the following code:


CREATE TABLE #adtMyTableListCheck ( 
dbName varchar(255), 
dbSchema varchar(255),
tblName varchar(255), 
tblType varchar(255)
);

SET @sql = N'Insert into #adtMyTableListCheck 
Select * From '+@databaseName+'.INFORMATION_SCHEMA.TABLES 
where TABLE_NAME like '''+@tablename+'''';

EXEC sp_executesql @sql; 
SELECT TOP 1 @dbSchema = IsNull(dbSchema,''), @dbTableType = IsNull(tblType,'')  FROM #adtMyTableListCheck;
DROP TABLE #adtMyTableListCheck;


First we create a temp table to store our results into.  Then we search the database in question for the table we are after.  If an object is found, we get the type and Schema of the object (the object owner).  The object could be view or a table, so later on we check that using the table type.  But, you might ask why do we get the object owner? By using the object owner in the queries it allows the queries execution plan to be cached.  Now, for the queries that we are talking about it probably .002 of a second at most, but if you do that 100 times a second, you start to get some savings.  If you do that 1000 times a second even more, and so on.

That is about all that I need to explain from the start part the overall flow of the stored procedure, which is:

• Gather inputs, sort out table names to use etc.
• Determine existence of the source table
• Insert a record into the audited table list
• Make sure that the database tables for the audit and archive tables have the required audit fields
• Make sure that the tables have the correct fields for the source table
• Create the trigger for the audit

So, we have created the audit and archive tables, as well as checked that the table exists, now we need to create the fields required for the database audit.