Share via

Recent changes table structure

Chaitanya Kiran 776 Reputation points
2021-09-15T12:20:52.75+00:00

Good Morning
How to find if there was any recent changes done to the table structure?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,361 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 43,246 Reputation points
    2021-09-15T12:31:15.21+00:00

    SQL Server don't track what changes on schema/object in detail was done, only that "something" was change.
    In SSMS do a right-mouse click on the database => Reports => Standard Report => Schema changes history.

  2. Erland Sommarskog 107.2K Reputation points
    2021-09-15T22:00:27.643+00:00

    As Olaf said, you can use the report in SSMS. It reads off the default trace. The default trace has five run-over files of fairly modest size. If you only see 24 hours back, I would say that is about what you can expect. But, hey, you said recent!

    If you want something that retains data longer, you will need to set up your own auditing. You can use SQL Server Audit for this.

    0 comments
  3. YufeiShao-msft 7,091 Reputation points
    2021-09-16T02:09:06.307+00:00

    Hi @Chaitanya Kiran ，

    if you can try to use triggers in SQL Server, DDL triggers are triggered when modification in structure appears
    https://learn.microsoft.com/en-us/sql/relational-databases/triggers/ddl-triggers?redirectedfrom=MSDN&view=sql-server-ver15

    or dig into transaction log, the transaction log contains the history of every action that modified anything in the database. Of course, it is usually seen as the last resort.
    https://rusanu.com/2014/03/10/how-to-read-and-interpret-the-sql-server-log/