Activity log of database MS SQL Server

Question

I have a database with more than a hundred tables. I am continuously adding columns to existing tables (if required) and I also added few new tables.

Now I want to check what changes I have made in last 3 months. Is there any activity log in MS SQL Server 2012 for that specific database to track changes.

Answer 1

Perhaps this can get you partway. sys.objects has create and modify dates but unfortunately sys.columns does not. However the latest columns added will have higher column_ids. I don't know that you would be able to pick out deleted columns that easily. Note that changes other than column changes can be reflected by the modify date.

select  s.name [schema], o.name [table], o.modify_date [table_modify_date], c.column_id, c.name
from    sys.schemas s
join    sys.objects o on o.schema_id = s.schema_id
left    join sys.columns c on c.object_id = o.object_id
where   o.type = 'U'    --user tables only
and     o.modify_date >= dateadd(M,-3, getdate())
order   by s.name, o.name, column_id;

To make this audit easier in the future you can create a DDL trigger that will log all schema changes to a table or in source control if you use something like a SSDT data project to manage your changes.

Answer 2

Right now,your options are limited ,going forward you can try below and also check to see if they help you, now..

1.If you have enabled Audit,you can track the changes

To check ,if you have enabled Audit,You can use below query..

select * from sys.dm_server_audit_status

If you have not enabled Audit,you can enable it ,following this :An Introduction to SQL Server Audit..I would not recommend enabling Audit ,unless you need to capture other than what is mentioned in your question

2.Default trace also captures tables created ,but this uses, roll over files mechanism to override last files when the space is full,so you may be out of luck(since you are asking for three months range),but try this:What event information can I get by default from SQL Server? to find out to know all the things,which are offered by default trace

I would go with this option and try backing up those files ,depending on when they rollup(since you need only to check table changes)

3.Finally One final option is to query Tlog

select * from fn_dblog(null,null) where [transaction name]='CREATE TABLE'

the above Tlog option works only if you have Tlog backups for over three months and also you need to restore them