information about modification of sql object

Question

information about modification of sql object

Vimal Lohani

SSCommitted

Points: 1650
More actions
September 25, 2014 at 7:01 am

#311883

Hi,
Is it possible to get all information about modification of sql objects,
For eg. I have stored procedure i have altered it 5 times, can i get all date time when it was modified & what changes done in it each time??
If possible how can i get that?
I know created date and last modified date maintained, but what about modification date time between these two?
Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

Viewing 11 posts - 1 through 10 (of 10 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You can look in the default trace, but it only keeps a short amount of history, 5 files of 20MB. Other than that, unless you have some DDL auditing, no.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Phil Parkin SSC Guru Points: 246985 More actions · Answer 2

As Gail intimated, something like this[/url] might be of interest to you.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 3

This is exactly why you should be using source control to modify all your database objects, just as developers use it to modify code. Unless you set up auditing, and collect and maintain that data over time, no, there's no way to get that information. But, if you do all your code changes in source control, that captures who made what changes over time. It's the right way to get this done.

"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

Vimal Lohani SSCommitted Points: 1650 More actions · Answer 4

Vimal Lohani (9/25/2014)
Hi,
Is it possible to get all information about modification of sql objects,
For eg. I have stored procedure i have altered it 5 times, can i get all date time when it was modified & what changes done in it each time??
If possible how can i get that?
I know created date and last modified date maintained, but what about modification date time between these two?

Couldn't get about the default trace.

Can't we get that from logs.

Is there any query??

Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

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

Grant Fritchey (9/25/2014)
This is exactly why you should be using source control to modify all your database objects, just as developers use it to modify code. Unless you set up auditing, and collect and maintain that data over time, no, there's no way to get that information. But, if you do all your code changes in source control, that captures who made what changes over time. It's the right way to get this done.

Actually, I'd suggest having both source control and a DDL trigger in place. Sometimes (3am call-out to fix the ETL) people have to make changes on production database objects without going through source control protocols. And those people don't always remember to post-fix source control.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Vimal Lohani (9/25/2014)
Can't we get that from logs.

No.

Is there any query??

No.

If it's not in the default trace, you haven't set up auditing and you aren't using source control, you cannot get this information.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 7

Phil Parkin (9/25/2014)
Grant Fritchey (9/25/2014)
This is exactly why you should be using source control to modify all your database objects, just as developers use it to modify code. Unless you set up auditing, and collect and maintain that data over time, no, there's no way to get that information. But, if you do all your code changes in source control, that captures who made what changes over time. It's the right way to get this done.
Actually, I'd suggest having both source control and a DDL trigger in place. Sometimes (3am call-out to fix the ETL) people have to make changes on production database objects without going through source control protocols. And those people don't always remember to post-fix source control.

Or, I can help you with that.[/url]

Actually, that does bring another option. You can look to third party tools to help with this. For example, running SQL Compare against your database and a snapshot of your database will at least show you when the changes have been done, if not who did them.

"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

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 8

I've found that the SSDT database projects and TFS works very well as the means of checking the DB against the Source Controlled Master version.

Though I agree with Phil on the DDL trigger idea, especially if you can get it to issue an alert email via SMTP to the DBA team with details of the object changed and person doing it for follow up the following day.

It should also set of alarms if someone is trying to circumvent the agreed release process.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 9

Quick thought, although source control systems are fine and in fact should always be in place, they are not holistic/comprehensive change management/monitoring/control systems. For those purposes they rely on a human property, discipline!

😎

ScottPletcher SSC Guru Points: 101120 More actions · Answer 10

No, such detailed change log info is not available. And when you think about it, you realize that it would be just too much overhead and disk space for SQL to do, particularly since no one might ever want to use it.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".