ThinkGeek! You"ll love this stuff!

Monday, September 24, 2007

Who changed my stored procedure?

/**********************************
** I currently work in an environment
** in which developers are
** allowed to make changes
** directly to the database objects
** in SQL Server.
**
** I have always endorsed empowerment
** and as a database developer/DBA for the
** same company, I do not feel threatened
** by their continued development in an
** environment under which they have this
** level of control.
**
** The problem arrises when we (as the database
** professionals on the development side of the house)
** are required to keep track of the data model and
** all objects. In order to accomplish this feat, we must be
** explicitly aware of all changes that occur. Well...
**
** ...here's a really cool tool that records these changes
** by taking advantage of features in SQL Server 2005
** namely the databse level trigger and the EVENTDATA()
** xml data type.
**
** What follows is a script that will allow you to see
** who is making changes to your objects in SQL
** Server 2005 databases.

** To see a full explanation of this solution
** by the author: Chris Rock (follow that link!!!)
**
** Happy TSQLing
** SQLPoet
************************************************/

USE [master]
GO
CREATE DATABASE [EventsLogDB]
GO
USE [EventsLogDB]
GO
--This goes into the eventslogdb database
CREATE TABLE [dbo].[ChangeLog](
[LogId] [int] IDENTITY(1,1) NOT NULL,
[DatabaseName] [varchar] (256) ,
[EventType] [varchar](50) ,
[ObjectName] [varchar](256) ,
[ObjectType] [varchar](25) ,
[SqlCommand] [varchar](max) ,
[EventDate] [datetime] NOT NULL CONSTRAINT [DF_EventsLog_EventDate] DEFAULT (getdate()),
[LoginName] [varchar](256) NOT NULL
) ON [PRIMARY]

--Note : Our DDL trigger goes into the database to monitor and is defined as:
USE [YourMonitoredDatabase]
GO
CREATE TRIGGER backup_objects
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 eventslogdb.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

No comments: