2

I am using SQL Server and a database has too many tables. I am preparing the list of tables which have been recently modified.

Is there any way to find out which tables are modified?

I am interested in knowing:

  • if any datatypes are modified to any columns,
  • new column is added,
  • any column is deleted.

Note The answers below shows tables which has been modified but do not give enough information of what changes has been done to the tables.

Improve this question
1
  • DDL auditing, or you could log changes from the system tables yourself.
    – S3S
    Aug 29, 2018 at 13:10

3 Answers 3

Reset to default
2

If you have not recently restarted your server, the default trace may have some info. In SSMS go to your DB, right click select reports -> Standard Reports -> "Schema Changes History", that should give you the changes on the DB, as long as there's info on your default trace.

Improve this answer
4
  • Thanks for the answer, I have followed as you suggested. I can see list of tables and procedures but how can I know what all changes has been done to a table. I can only create,drop or alter under DDL Operation
    – SpringLearner
    Aug 29, 2018 at 15:06
  • If you need that kind of detail, you may need to implement a DDL trigger, in that case you can set the behavior to capture the old structure and the new structure.
    – dbamex
    Aug 29, 2018 at 15:28
  • please guide me how to implement trigger
    – SpringLearner
    Aug 30, 2018 at 12:39
  • This should help. But this will help you with future alteration on your objects. You will not get any changes that were done in past.
    – UdIt Solanki
    Sep 3, 2018 at 11:03
2

You can check the modify_date column in either the sys.tables or sys.all_objects (with type = 'U' for user table) system views. The below query will work, note that this cast defaults to midnight.

select name from sys.all_objects where type = 'U' and
modify_date >= cast('2018-08-22' as datetime)
Improve this answer
1
  • Thanks for answering but how do I know what columns are added or deleted?
    – SpringLearner
    Aug 30, 2018 at 12:42
0

As per your comments to implement trigger. I would suggest you to create DDL triggers for tracking DDL changes. One out of box solution for this is you can implement the scripts by David Rueter which he explains here I have implemented this in dev and test servers and it's pretty good. its hassle free. The SP changes, DDL changes everything is captured with a versioning. So you can easily roll back to an older version. The only drawback I found if you rename the table from SSMS it's not capturing. I think its raised in the comment section.

Improve this answer
2
  • Thanks for your answer. But I have not implemeneted trigger so how can I know what all tables have already been modified. Trigger will be useful for me for future table modifications but how would I know the changes been done to earlier
    – SpringLearner
    Sep 3, 2018 at 14:03
  • @SpringLearner, AFAIK, yes you can't get the modifications done earlier. All you can get the changes before restart as mentioned by dbamex.
    – Biju jose
    Sep 3, 2018 at 14:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.