First off we need a database and a table to hold the list of changes.
CREATE DATABASE SQLSourceSafe
GO
USE SQLSourceSafe
GO
CREATE TABLE dbo.ProcedureChanges
(
[ID] uniqueidentifier NOT NULL ROWGUIDCOL,
[Server] VARCHAR(64),
IP_Address VARCHAR(32),
[Database] NVARCHAR(255),
[Schema] NVARCHAR(255),
[Object] NVARCHAR(255),
[Date] DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
[Type] NVARCHAR(64),
ProcName NVARCHAR(255),
SourceCode NVARCHAR(MAX),
SourceCodeXML XML,
UserName NVARCHAR(255)
)
ALTER TABLE dbo.ProcedureChanges ADD CONSTRAINT ProcedureChanges_ID DEFAULT (newid()) FOR ID
ALTER TABLE dbo.ProcedureChanges ADD CONSTRAINT ProcedureChanges_PK_ID PRIMARY KEY CLUSTERED ( ID )
WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
So now we have our table we can get a current list of all the procedures in the databases that we want to start auditing. We can easily do this by looking in the sys.procedures table, this holds all the data on the current procedures in the database. So from YOUR database, run the following code.
INSERT INTO SQLSourceSafe.dbo.ProcedureChanges ( [Type],[SourceCode],[Database],[Schema],[Object],[UserName] ) SELECT 'CREATE_PROCEDURE', OBJECT_DEFINITION([object_id]), DB_NAME(), OBJECT_SCHEMA_NAME([object_id]), OBJECT_NAME([object_id]), 'Stewart Millward' FROM sys.procedures
Notice I use the actual procedure and schema names? I prefer to be able to see the names of the procuedures rather than run around trying to find them. Anywho that’s all the information on the current look of the database now it’s time to record the changes made to the database from now on, for this we chall use a trigger on the events we know change a procedure and then just shove similar code to above in there.
CREATE TRIGGER SourceSafe_ProcedureTrigger
ON DATABASE FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, ALTER_SCHEMA, RENAME
AS
BEGIN
SET NOCOUNT ON
DECLARE @EventData XML = EVENTDATA()
DECLARE @ip VARCHAR(32) = (SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID)
INSERT INTO SQLSourceSafe.dbo.ProcedureChanges
( [Type], [SourceCode], [SourceCodeXML], [Database], [Schema], [Object], [Server], [IP_Address], [ProcName], [UserName] )
SELECT @EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),
@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
@EventData,
DB_NAME(),
@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)'),
@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'),
HOST_NAME(),
@ip,
PROGRAM_NAME(),
SUSER_SNAME()
END
GO
And that’s that it will start logging the changes in the database for you and you can then always go back should a mistake be made… huzzar!
Just some notes, RENAME is a 2008 event only so you may get an error on a 2005 machine, also if you want to disable the trigger you can use the following code.
DISABLE TRIGGER [SourceSafe_ProcedureTrigger] ON DATABASE
That’s That.
