I work in an environment where too many people have too many permissions across all of our servers from development to production. Since we do not typically go out and purchase outside software, it was my pleasure to return once again and mine SQL Server system views for the information I wanted.
I quickly found that the sys.objects table alone was not doing the trick since the modified date can update for various reasons, such as a dropped index. We often drop indices before loading data and recreate them after the load is finished. Which leads to another problem: the newly created index has a newly created object_id to go with it. Who needs to deal with ever changing object IDs? The solution is to track the true primary keys of objects and ignore object_id altogether.
The top-most level, "object", I defined thus:
server_name varchar(128) not null
,database_name varchar(128) not null
,[schema_name] varchar(50) not null
,[object_name] varchar(128) not null
,type_desc varchar(60) not null
Some column definitions were modified for the sake of my own indexes, because of the 900 byte restriction.
For object definition, we get the colid and text columns from sys.syscomments, and the primary key of the "object definition" table is expanded to include colid.
For table schema, we get the ordinal_position from both information_schema.columns (tables and views) & information_schema.routine_columns (table valued functions). So, the primary key of the "table schema" table is expanded to include ordinal_position.
The stored procedures usp_monitor_scan and usp_monitor_db_merge are the key sprocks. Scan is for your daily batch and Merge is used to initialize a newly added database monitor or to merge changes after you have had a chance to review them. This does not mean changes were prevented from happening before running the merge! The merge simply drops all the flagging and delta records created when change was observed during a Scan.
The update triggers double-log both the deleted version (B for before) and the inserted version (A for after).
A few tables and the activity logging procedure were not included in the script. I leave reverse-engineering them as an exercise.