February 10, 2009 at 8:44 am
How can I find out the objects (sp/tables) which are altered on a particular date or when it is last altered?
---------------------------------------------------
Thanks,
Satheesh.
February 10, 2009 at 8:52 am
The default trace is a good place to start, but it will only have as much information as the number of rollover files which is very short.
http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
February 10, 2009 at 8:53 am
Satheesh Kumar (2/10/2009)
How can I find out the objects (sp/tables) which are altered on a particular date or when it is last altered?
-- Tables
select * from sys.objects where type = 'U'
order by modify_date desc
--Trigger
select * from sys.objects where type = 'TR'
order by modify_date desc
--Procedure
select * from sys.objects where type = 'P'
order by modify_date desc
--Function
select * from sys.objects where type = 'FN'
order by modify_date desc
February 10, 2009 at 8:54 am
Default trace will have some of that.
If you want more, you pretty much need to set up DDL triggers and log the changes. That's pretty easy to do.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 10, 2009 at 11:08 am
Thanks Paresh Prajapati. I checked only in sys.sysobjects before posting :rolleyes:.
Thanks GSquared, I will consider using DDL Triggers. Can you help me with best practice documents?
Thanks for everyone.
---------------------------------------------------
Thanks,
Satheesh.
February 10, 2009 at 11:09 am
Books Online and http://www.msdn.com have data on how to use DDL triggers to audit database changes. Look them up in there, you'll get what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 11, 2009 at 3:00 am
Thanks.
---------------------------------------------------
Thanks,
Satheesh.
February 11, 2009 at 5:57 am
Erode P. Satheesh (2/10/2009)
Thanks Paresh Prajapati. I checked only in sys.sysobjects before posting :rolleyes:.Thanks GSquared, I will consider using DDL Triggers. Can you help me with best practice documents?
Thanks for everyone.
U can use following DDL trigget to track of changed objects:
CREATE TABLE [dbo].[AdministratorLog](
[LogId] [int] IDENTITY(1,1) NOT NULL,
[DatabaseName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectType] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SqlCommand] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventDate] [datetime] NOT NULL CONSTRAINT [DF_EventsLog_EventDate] DEFAULT (getdate()),
[LoginName] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_tbl_ChangeLog] PRIMARY KEY CLUSTERED
(
[LogId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TRIGGER [Admin_BackupObjects]
ON DATABASE
FOR
DDL_DATABASE_LEVEL_EVENTS
--create_trigger,alter_trigger,drop_trigger,
--create_procedure, alter_procedure, drop_procedure,
--create_table, alter_table, drop_table,
--create_function, alter_function, drop_function,
--create_view,alter_view,drop_view
AS
BEGIN
SET NOCOUNT ON
DECLARE @data XML
SET @data = EVENTDATA()
INSERT INTO AdministratorLog(DatabaseName,EventType,ObjectName,ObjectType,SqlCommand,LoginName)
VALUES(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'), -- value is case-sensitive
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_PADDING ON
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply