possible to see when a piece of text was removed from an SP?

Question

possible to see when a piece of text was removed from an SP?

davidandrews13

SSCertifiable

Points: 6808
More actions
July 10, 2012 at 2:52 am

#260908

i have a Stored Procedure (SP1) which calls multiple other Stored Procedures.
I've noticed that a call to one Stored Procedure is not being made from SP1.
I'm wondering whether it was ever there, so... is it possible to find if it was, who may have removed it and at what date/time?
I have a piece of code, as below, that tells me when an object has been created and modified, but not what that modification was.
SELECT name, type_desc,create_date,modify_date, [type]
FROM sys.objects
--WHERE type = 'P'
WHERE --DATEDIFF(D,modify_date, GETDATE()) < 10
--and
name = 'my_SP'
order by modify_date

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

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 1

Nope. SQL Server doesn't store versioned information about the objects inside of it. There's no way to get that directly. My recommendation, put all your database T-SQL code into source control. That way you have a managed, versioned view of the objects. If you always deploy changes from source control, then you can also know who made changes as well as understanding when they occurred.

"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

davidandrews13 SSCertifiable Points: 6808 More actions · Answer 2

aah well, never mind. thanks anyway 🙂

I shall have a look at Source Control products out there and what they do.

cheers

crazy4sql SSCoach Points: 19590 More actions · Answer 3

i have a Stored Procedure (SP1) which calls multiple other Stored Procedures.
I've noticed that a call to one Stored Procedure is not being made from SP1.
I'm wondering whether it was ever there, so... is it possible to find if it was, who may have removed it and at what date/time?

you can try to search the old backup and restore with some other name. If SP is there then you will be able to see the difference(if the restore is old enough to verify)

----------
Ashish

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 4

there is a report that you can run that till tell you who last changed the stored procedure and when. from ssms navigate to your database, right mouse click on the database, select reports, standard reports, schema changes.

Also look at DDL triggers, i use a DDL triger to audit all stored procedure changes

***The first step is always the hardest *******

crazy4sql SSCoach Points: 19590 More actions · Answer 5

there is a report that you can run that till tell you who last changed the stored procedure and when. from ssms navigate to your database, right mouse click on the database, select reports, standard reports, schema changes.

only if the audit is enabled at instance level and sql services not restarted.

----------
Ashish

davidandrews13 SSCertifiable Points: 6808 More actions · Answer 6

SGT_squeequal (7/10/2012)
there is a report that you can run that till tell you who last changed the stored procedure and when. from ssms navigate to your database, right mouse click on the database, select reports, standard reports, schema changes.
Also look at DDL triggers, i use a DDL triger to audit all stored procedure changes

thanks. unfortunately, i am now the last person to change the stored procedure as i added the stored procedure call back in 😀

i'll remember this for next time though 😎

Lowell SSC Guru Points: 323518 More actions · Answer 7

crazy4sql (7/10/2012)
there is a report that you can run that till tell you who last changed the stored procedure and when. from ssms navigate to your database, right mouse click on the database, select reports, standard reports, schema changes.
only if the audit is enabled at instance level and sql services not restarted.

no, i think that's just a standard report that hits the default trace, which is enabled by default.

the default trace is limited to the last 100 meg of DDL changes ont eh whole server, so one developer database that gets lots of changes can push other changes out of the log as it gets reused, so time is important when you grab this report.

so that will give you a list of who changed it , if it hasn'be been aged out of the default trace.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!