Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Track changes to database objects


Track changes to database objects

Author
Message
Manie Verster
Manie Verster
Default port
Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)

Group: General Forum Members
Points: 1433 Visits: 1022
I need to supply my manager with a monthly report showing all the database objects that was changed. E.g. stored procedures created or modified as well as functions, triggers, tables etc. Is there anyone that can help me to do that? I do have a trigger running on my database and inserting the administratorlog table which I will include below but with table changes it does not give me the column name that was add/altered. Also it gives the whole sql statement of a stored procedure that was altered. I am not sure if more specific detail can be given but if it can I would like to hear about it.

Create administratorlog table script.


CREATE TABLE [dbo].[administratorlog](
[idkey] [int] IDENTITY(1,1) NOT NULL,
[databasename] [nvarchar](256) NULL,
[eventtype] [nvarchar](50) NULL,
[objectname] [nvarchar](256) NULL,
[objecttype] [nvarchar](25) NULL,
[sqlcommand] [nvarchar](max) NULL,
[loginname] [nvarchar](256) NULL,
CONSTRAINT [PK_administratorlog] PRIMARY KEY CLUSTERED
(
[idkey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]



The trigger script


CREATE TRIGGER [Admin_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 dbo.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)'))
GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [Admin_Backup_Objects] ON DATABASE
GO



Thanks in advance.

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Lowell
Lowell
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18852 Visits: 39438
Manie you script looks pretty good without testing it myself;

Here's a something similar I built for some unknown forum post here; the basic differences are i'm also capturing changes to VIEWS and TRIGGERS, and maybe capturing a few more whodunnit fields, but it's basically exactly the same as yours:

use master
CREATE TABLE [dbo].[DDLEVENTLOG] (
[DATABASENAME] SYSNAME NOT NULL,
[EVENTDATE] DATETIME NULL,
[USERNAME] SYSNAME NULL DEFAULT (getdate()),
[SYSTEMUSER] VARCHAR(128) NULL,
[CURRENTUSER] VARCHAR(128) NULL,
[ORIGINALUSER] VARCHAR(128) NULL,
[HOSTNAME] VARCHAR(128) NULL,
[APPLICATIONNAME] VARCHAR(128) NULL,
[SCHEMANAME] SYSNAME NULL,
[OBJECTNAME] SYSNAME NULL,
[OBJECTTYPE] SYSNAME NULL,
[EVENTTYPE] VARCHAR(128) NULL,
[EVENTDATA] XML NULL,
[COMMANDTEXT] VARCHAR(max) NULL,
[OBJECTDEFINITION] VARCHAR(max) NULL)
GO
use SandBox
GO
ALTER TRIGGER [ReturnPREventData]
ON DATABASE
WITH EXECUTE AS 'dbo'
FOR
CREATE_PROCEDURE, DROP_PROCEDURE, ALTER_PROCEDURE,
CREATE_FUNCTION, DROP_FUNCTION, ALTER_FUNCTION,
CREATE_VIEW, DROP_VIEW, ALTER_VIEW,
CREATE_TABLE, DROP_TABLE, ALTER_TABLE,
CREATE_TRIGGER, DROP_TRIGGER, ALTER_TRIGGER
AS
BEGIN
SET NOCOUNT ON
declare @ObjectDef table(definition varchar(max))
declare
@eventData XML,
@DATABASENAME SYSNAME,
@EVENTDATE DATETIME,
@USERNAME SYSNAME,
@SYSTEMUSER VARCHAR(128),
@CURRENTUSER VARCHAR(128),
@ORIGINALUSER VARCHAR(128),
@HOSTNAME VARCHAR(128),
@APPLICATIONNAME VARCHAR(128),
@SCHEMANAME SYSNAME,
@OBJECTNAME SYSNAME,
@OBJECTTYPE SYSNAME,
@EVENTTYPE VARCHAR(128),
@COMMANDTEXT VARCHAR(max),
@NAMEFORDEFINITION VARCHAR(261)

--Load Variables from the xml
SET @eventData = eventdata()
SELECT
@DATABASENAME = db_name(),
@EVENTDATE = GETDATE(),
@USERNAME = @eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),
@SYSTEMUSER = SUSER_SNAME(),
@CURRENTUSER = CURRENT_USER,
@ORIGINALUSER = ORIGINAL_LOGIN(),
@HOSTNAME = HOST_NAME(),
@APPLICATIONNAME = APP_NAME(),
@SCHEMANAME = @eventData.value('data(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME'),
@OBJECTNAME = @eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
@OBJECTTYPE = @eventData.value('data(/EVENT_INSTANCE/ObjectType)[1]', 'SYSNAME'),
@COMMANDTEXT = @eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VARCHAR(MAX)'),
@EVENTTYPE = @eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)')
--get the object definition
SET @NAMEFORDEFINITION = '[' + @SCHEMANAME + '].[' + @OBJECTNAME + ']'
INSERT INTO @ObjectDef(definition)
EXEC sp_getDDL @NAMEFORDEFINITION
--now save the audit info
INSERT [master].[dbo].[DDLEventLog] (DATABASENAME,EVENTDATE,USERNAME,SYSTEMUSER,CURRENTUSER,ORIGINALUSER,HOSTNAME,APPLICATIONNAME,SCHEMANAME,OBJECTNAME,OBJECTTYPE,EVENTTYPE,EVENTDATA,COMMANDTEXT,OBJECTDEFINITION)
SELECT
@DATABASENAME,
@EVENTDATE,
@USERNAME,
@SYSTEMUSER,
@CURRENTUSER,
@ORIGINALUSER,
@HOSTNAME,
@APPLICATIONNAME,
@SCHEMANAME,
@OBJECTNAME,
@OBJECTTYPE,
@EVENTTYPE,
@eventData,
@COMMANDTEXT,
definition
FROM @ObjectDef
END --DB TRIGGER
GO
ENABLE TRIGGER [ReturnPREventData] ON DATABASE
GO




Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Manie Verster
Manie Verster
Default port
Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)Default port (1.4K reputation)

Group: General Forum Members
Points: 1433 Visits: 1022
Thanks for the script. I just need you to please post me the script for sp_getDDL then I can fully test this solution and see if it will work for me. I appreciate your trouble very much and thank you for your help.

:-PManie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Lowell
Lowell
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18852 Visits: 39438
oops sorry: it's from an article i posted here:
sp_GetDDL_Latest.txt (scripts any object, returns varchar(max)
sp_GetDDLa_Latest.txt (scripts any object, returns multi row recordset)


Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Eric Mueller
Eric Mueller
SSC Veteran
SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)

Group: General Forum Members
Points: 221 Visits: 677
This can also be done using RedGate SQL Compare command line. You can create a batch that takes a snapshot of the databases that you are interested in and then compare against the prior months snapshot.

SQL Compare will also allow you to create a separate HTML file for each compared datatabase. This would not only indicate the objects that have changed, but also how they changed. We have been running this for several years.

Since this does not rely on triggers you do not have to be concerned with what occurs during times that the trigger is disabled.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search