When was db object last updated?

Question

When was db object last updated?

sql_er

SSCarpal Tunnel

Points: 4135
More actions
April 4, 2007 at 10:38 am

#61890

Guys,
Is there a way to find out when a certain DB object (e.g. Stored procedure) was last modified?
SYSOBJECTS table contains crdate and refdate fields. None of these, however, appear to tell me when when the object was last updated.
Any suggestions?
Thanks a lot

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

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

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 1

No such thing in SQL Server 2000. You can make one with a trigger on each table and a "LastModified" table... just be careful not to make any deadlocks...

--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)

scb SSCommitted Points: 1859 More actions · Answer 2

In SQL 2005, you can use DDL triggers to keep audit trails of object changes as well.....something like.....

CREATE TABLE [dbo].[DDLAuditLog](

[EventType] [nvarchar](100) NULL,

[XMLCommand] [xml] NULL,

[PostTime] [datetime] NULL,

[HostName] [nvarchar](100) NULL,

[LoginName] [nvarchar](100) NULL

)

GO

CREATE TRIGGER [dbtr_AuditOperations]

ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

AS

DECLARE @data XML

DECLARE @posttime datetime

DECLARE @hostname NVARCHAR(100)

DECLARE @loginname NVARCHAR(100)

DECLARE @eventtype NVARCHAR(100)

-- Retrieve the event data XML

SET @data = eventdata()

-- Extract data from the XML

SET @posttime = CONVERT(NVARCHAR(24),@data.query('data(//PostTime)'))

SET @eventtype = CONVERT(NVARCHAR(100),@data.query('data(//EventType)'))

-- Retrieve other system information

SET @hostname = HOST_NAME()

SET @loginname = SYSTEM_USER

-- Write event data to the log table

INSERT INTO dbo.DDLAuditLog(EventType, XMLCommand, PostTime,HostName,LoginName)

VALUES(@eventtype, @data, @posttime, @hostname, @loginname)

GO

ENABLE TRIGGER [dbtr_AuditOperations] ON DATABASE

GO

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

In SQL 2000, the "version" field will change on alters. May change on a few other index things, but alters for sure. You can track this

http://www.sqlservercentral.com/columnists/sjones/20010423065956_1.asp

Doesn't give you a date unless you track it everyday.

sql_er SSCarpal Tunnel Points: 4135 More actions · Answer 4

Thanks for the suggestions guys.

Steve: I am reading your link now ... very useful!

Steve Jones - SSC Editor SSC Guru Points: 736297 More actions · Answer 5

Hope it helps. Remember to that unless you track it regularly, like every day, you won't know when it changed.

sql_er SSCarpal Tunnel Points: 4135 More actions · Answer 6

sql_er

SSCarpal Tunnel

Points: 4135

April 10, 2007 at 2:44 pm

#699647

Yes, I am aware of that.

Thank you