Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Track changes to database objects Expand / Collapse
Author
Message
Posted Tuesday, March 19, 2013 5:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:21 AM
Points: 1,205, Visits: 923
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.


Manie 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)
Post #1432572
Posted Tuesday, March 19, 2013 5:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:26 AM
Points: 12,905, Visits: 32,164
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1432584
Posted Tuesday, March 19, 2013 7:07 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 5:21 AM
Points: 1,205, Visits: 923
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.

Manie 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)
Post #1432629
Posted Tuesday, March 19, 2013 7:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:26 AM
Points: 12,905, Visits: 32,164
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1432637
Posted Wednesday, March 20, 2013 9:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 4, 2014 10:47 AM
Points: 221, Visits: 663
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.
Post #1433297
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse