June 22, 2009 at 10:28 pm
Hi,
i want to trace the no. of times the stored procedure is modified along with date and time. i want dates and times the no. of the times the SP is being modified.
Can some body plz assist me?
June 23, 2009 at 12:58 am
You can use database triggers to accomplish this task.
I use this simple one:
1) Code for destination table:
CREATE TABLE [dbo].[ChangeLog](
[LogId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[DatabaseName] [varchar](256) NOT NULL,
[EventType] [varchar](50) NOT NULL,
[ObjectName] [varchar](256) NOT NULL,
[ObjectType] [varchar](25) NOT NULL,
[SqlCommand] [varchar](max) NOT NULL,
[EventDate] [datetime] NOT NULL,
[LoginName] [varchar](256) NOT NULL,
)
2) Code for db trigger:
create trigger [TR_Auditing]
on database
for create_procedure, alter_procedure, drop_procedure,
create_table, alter_table, drop_table,
create_function, alter_function, drop_function
as
set nocount on
declare @data xml
set @data = EVENTDATA()
insert into dbo.changelog(
databasename, eventtype,
objectname, objecttype,
sqlcommand, loginname)
values(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
@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)')
)
GO
Hope this helps.
Gianluca
-- Gianluca Sartori
June 23, 2009 at 1:10 am
select name,modify_date from sys.objects where type ='P'
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 23, 2009 at 1:11 am
Thank you for your quick reply..
i have created the table and trigger in my database and i did some modifications in one stored procedure. when i clicked on execute button following is the error message i got.
Cannot insert the value NULL into column 'EventDate', table 'master.dbo.ChangeLog'; column does not allow nulls. INSERT fails.
The statement has been terminated.
can u plz tell y this is happing.
June 23, 2009 at 1:16 am
insert into dbo.changelog(
databasename, eventtype,
objectname, objecttype,
sqlcommand,eventdate, loginname)
values(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
getdate(),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)
June 23, 2009 at 1:18 am
hi_abhay78 (6/23/2009)
select name,modify_date from sys.objects where type ='P'
Ok, this works but does not return the number of times the procedure is modified. Anyway is a nice way to return last modified date.
-- Gianluca Sartori
June 23, 2009 at 1:19 am
vyelchri (6/23/2009)
Thank you for your quick reply..i have created the table and trigger in my database and i did some modifications in one stored procedure. when i clicked on execute button following is the error message i got.
Cannot insert the value NULL into column 'EventDate', table 'master.dbo.ChangeLog'; column does not allow nulls. INSERT fails.
The statement has been terminated.
can u plz tell y this is happing.
Sorry, you need to add a default value to the destination table:
ALTER TABLE [dbo].[ChangeLog] ADD CONSTRAINT [DF_EventsLog_EventDate] DEFAULT (getdate()) FOR [EventDate]
GO
I would also suggest not to apply the script to the master database, as I see you are doing by the message you get.
Regards
Gianluca
-- Gianluca Sartori
June 23, 2009 at 1:29 am
Modify that colume :
CREATE TABLE [dbo].[ChangeLog](
[LogId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[DatabaseName] [varchar](256) NOT NULL,
[EventType] [varchar](50) NOT NULL,
[ObjectName] [varchar](256) NOT NULL,
[ObjectType] [varchar](25) NOT NULL,
[SqlCommand] [varchar](max) NOT NULL,
[EventDate] [datetime] default getdate(), <--here it is
[LoginName] [varchar](256) NOT NULL)
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 23, 2009 at 2:36 am
Thank you very much
i am able to trace the all the modifications which are done to the database objects.
is it possible to get from which system these objects are being modified?
June 23, 2009 at 2:45 am
You can get the application name from the connection properties:
select program_name from master.dbo.sysprocesses where spid = @@spid
But I don't know if this is exactly what you need.
-- Gianluca Sartori
June 23, 2009 at 2:47 am
Obviously you would have to add a new column to the trace table ad add the code to populate it in the db trigger.
-- Gianluca Sartori
June 23, 2009 at 2:53 am
Thank you very much
i got the solution to get machine name. i used host_name() function to get system name and added one more column to the ChangeLog table with default value as "Host_name()"
Once again thank you.
June 23, 2009 at 2:55 am
Glad I could help
-- Gianluca Sartori
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply