How to track Stored Procedure Changes

Question

Post reply

How to track Stored Procedure Changes

Manish Sinha

Ten Centuries

Points: 1023
More actions
July 24, 2008 at 5:46 pm

#123368

How can I track Stored Procedure Changes?If I want to check that which SQL user modified the SP on which date,is there any way I can do it?I am using SQL Server 2005.What the rights needed for this?

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

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

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 1

Can you look at the report in studio standard reports, schema changes history may have this in here for change of sp.

Manish Sinha Ten Centuries Points: 1023 More actions · Answer 2

Thanks Tracey! But that shows history for around 10-11 days only.I need around one month history.Can you plz help me with this?

Steve Jones - SSC Editor SSC Guru Points: 734498 More actions · Answer 3

I'm not sure what the default time that the data is stored, but I bet that if the report only goes back 10 days, the data is gone. This isn't maintained indefinitely as it could be a space issue.

einman33 SSCrazy Points: 2813 More actions · Answer 4

Maybe consider rolling your own DDL trigger functionality.

michaela SSCertifiable Points: 5649 More actions · Answer 5

CREATE TABLE [dbo].[DDLEventLog](

[EventDate] [datetime] NOT NULL,

[UserName] [sysname] NOT NULL,

[objectName] [sysname] NOT NULL,

[CommandText] [varchar](max) NOT NULL,

[EventType] [nvarchar](100) NULL

) ON [PRIMARY]

--

create trigger [ReturnEventData]

on DATABASE

FOR

CREATE_PROCEDURE, DROP_PROCEDURE, ALTER_PROCEDURE

AS

declare @eventData XML,

@uname nvarchar(50),

@oname nvarchar(100),

@otext varchar(max),

@etype nvarchar(100),

@edate datetime

SET @eventData = eventdata()

SELECT

@edate=GETDATE(),

@uname=@eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),

@oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),

@otext=@eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',

'VARCHAR(MAX)'),

@etype=@eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')

if @uname is not null

begin

insert dbo.DDLEventLog (EventDate, UserName, objectName, CommandText,EventType) values

(@edate,@uname,@oname,@otext,@etype)

end

GO

ENABLE TRIGGER [ReturnEventData] ON DATABASE

Lowell SSC Guru Points: 323504 More actions · Answer 6

awesome example michaela;

Still dealing with too much SQL 2000 here, and i wanted a nice working example to explore the new DDL trigger abilities.

I was able to read it, understand it, and extend it as examples for changes in views,tables and functions as well.

Thank you.

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!

Manish Sinha Ten Centuries Points: 1023 More actions · Answer 7

Thanks Michaela!!

I wanted the history of the SP which is already modified few times during last 2-3 weeks.But this will help me to track it in future.Thanks for your great help.