question

ChaitanyaKiran-2787 avatar image
0 Votes"
ChaitanyaKiran-2787 asked ErlandSommarskog commented

Recent changes table structure

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

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered TomPhillips-1744 commented

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Olaf,

It is showing only from last 24 hours. How to find recent changes done to the table structure?

0 Votes 0 ·

That information does not exist. SQL Server does not save that information.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yufeishao-msft avatar image
0 Votes"
Yufeishao-msft answered ErlandSommarskog commented

Hi @ChaitanyaKiran-2787

if you can try to use triggers in SQL Server, DDL triggers are triggered when modification in structure appears
https://docs.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/

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yeah, DDL triggers are absolutely a valid option, and I may even prefer that over SQL Server Audit.

Although, a potential problem is that a DDL trigger may be disabled without a trace. (This is more difficult to do with audit.)

0 Votes 0 ·