September 30, 2015 at 3:07 pm
Hello everyone,
I have a server that has 8 user databases. This is a DEV/QA database with several users having permissions to create/alter/drop various DB objects (Tables, Views, Stored Procedures, Functions etc). I need to prepare a report at the end of each day with a list of all such changes. I wondering what would be the most efficient way to capture this data. FYI, I do not need to report what changes were done to SPs, functions, views. All I need is the date and time at which the change took place. However, table changes it would be nice if I can capture what columns were added/altered/deleted.
Thanks in advance,
SQLCurious
October 1, 2015 at 7:50 am
I'm not aware of any way to do this through DMVs. A number of other options present themselves, but all are going to be a lot more work than just querying for the existing data.
1) Get your objects in source control. This is a good practice anyway. If you do database development the way you do app development and nothing exists on the database unless it's in source control, you can easily generate a report of what changes were made over the last day.
2) Script out a copy of the database each day and then compare the objects to the previous day.
3) Set up triggers on the database to capture when DDL commands are send and write those out to a log somewhere.
4) Set up extended events to capture the changes and write those out to a file from which you can build your report.
5) Get a third party tool (like Redgate SQL Compare) that can compare between a backup, a script, or source control, and your database to arrive at the changes.
Personally, the first option is the best since this is something that ought be done through a solid development process anyway.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 20, 2015 at 12:36 pm
@Grant: Thanks for the advice.
Here is an updated situation. There is a trace file running already that is capturing all the events triggered by a certain set of users (profile has a filter setup for these users). What would be specific queries that I can use to get just the DML and DDL statements triggered by these users? Is this possible?
SQLCurious
October 20, 2015 at 12:42 pm
The following will return all objects modified today.
select * from sys.objects where cast(modify_date as date) = cast(getdate() as date);
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 20, 2015 at 1:16 pm
Thanks, Eric. Unfortunately, that's not enough for me since it will only tell me that the object has been modified...that too the most recent one. I need to know what was modified/by whom/when. If there are multiple modifications, I need to capture and report them all. As I mentioned earlier, capturing part is already being done.
SQLCurious
October 21, 2015 at 7:10 am
SQLCurious (10/20/2015)
Thanks, Eric. Unfortunately, that's not enough for me since it will only tell me that the object has been modified...that too the most recent one. I need to know what was modified/by whom/when. If there are multiple modifications, I need to capture and report them all. As I mentioned earlier, capturing part is already being done.SQLCurious
Try searching on "SQL Server DDL events"
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
October 21, 2015 at 7:22 am
If I had to deal with something like this, I'd go to extended events. You can capture events like object_altered, object_created and object_deleted to see what was done within the system. The events include database_id, ddl_phase, index_id, object_id, object_name, object_type, related_object_id and transaction_id. You can add the Actions like client_hostname to get the name of the machine calling for the change, nt_username to get the login, or username to get the SQL login.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply