Rather than posting a very long Oracle procedure, I will just describe what the Oracle procedure does. First, let me say my solution is derived from an elegant solution from an article by Sanjay Ray (http://www.oracle.com/technology/oramag/code/tips2004/012504.html). I was hoping there was an equivalent SQL Server elegant solution that I could adapt. If not, then perhaps this will make a very good article for someone when I figure it out. 🙂

Problem: I have a copy of TableA from yesterday and today. I want to know specifically what changed. The adds and deletes are simple, using the key and the EXCEPT and UNION ALL operators (see http://weblogs.sqlteam.com/jeffs/archive/2007/05/02/60194.aspx). For modifications, I want to know the key for the modified records and then the fields that were changed as well as the old and new values. This needs to be in a generic stored procedure so I can audit any two tables.

Solution: The trick is building a huge dynamic SQL statement. Pass the proc two tablenames, one of which is the "base" table for the comparison. The key structure is determined by looking up the fields in the metadata for indexes for the base table. If there is no index (there should be), the first column is used. The two tables will be joined by the key fields and every other field will be compared.

Using the list of columns from the metadata (INFORMATION_SCHEMA.Columns in SS), the query is built for the non-key fields. The SELECT list is built dynamically from the metadata into a concatenated list for each field. Each field will end up looking something like :

CASE

WHEN A.column_name = B.column_name THEN null

ELSE 'column_name=['+A.column_name+'->'+B.column_name+']'

END

There is a delimiter (~) between each field in the SELECT list. So if the fields are equal the null will result in ~~ and if they are unequal, then the name of the column and the values from each table are put between the delimiters.

The dynamic SQL is actually an INSERT statement into another table. This table is then parsed to find the data between the delimiters and report the field names and the old and new values.

Voila! I hope any of this made sense. As I work through each of the processes, I may return with more detailed questions. Right now I was just hoping someone else had already published a solution for SQL Server.