Recent changes table structure

Question

Recent changes table structure

coolchaitu

SSChampion

Points: 10334
More actions
September 15, 2021 at 12:23 pm

#3929780

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246956 More actions · Answer 1

Do a DB schema compare against what you have stored in source control to identify any 'drift'.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Grant Fritchey SSC Guru Points: 398880 More actions · Answer 2

Yep. What Phil says.

Otherwise, you can set up an Extended Events session to capture object_altered. Same goes for created and dropped.

"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

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 3

coolchaitu wrote:

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

A quick way to check is to query against sys.tables in a database. Every table has a "modify_date" column. That date will either be the create_date if the table has never been modified or it will contain the date of the last ALTER TABLE done.

It won't tell you what the changes are but you can write a pretty simple query to tell you when the last change was. If you write a query to check if there are any modify_date's within the last seven days and write a quick little job that will send you an email when such a date appears, you can have a really good "it just happened" notification system.

If you want to know what the changes actually are, then you'd have to have a system in place to do checks or capture changes like Phil and Grant what have respectively posted about above.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

coolchaitu SSChampion Points: 10334 More actions · Answer 4

Hi Jeff,

Does the "modify_date" column gets modified when DML changes also occur, or only DDL changes?

Phil Parkin SSC Guru Points: 246956 More actions · Answer 5

coolchaitu wrote:

Hi Jeff,
Does the "modify_date" column gets modified when DML changes also occur, or only DDL changes?

Why not run a quick test and find out for yourself?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 6

coolchaitu wrote:

Hi Jeff,
Does the "modify_date" column gets modified when DML changes also occur, or only DDL changes?

You should do a search on sys.tables instead of taking my word for it. I'll also advise that there will be an "inherited columns" link that you can and should click on to see any columns that are missing. This will also teach you a bit about sys.objects, which is one of "those" things that is an absolute MUST to learn how to use. In fact, you should look that up, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 7

There is also the schema changes report available - that shows not only changes to tables but will also show changes to any objects and who made the change. However, since it pulls the data from the default trace - it may not go back far enough if you have enough activity to roll the default trace.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs