March 19, 2013 at 5:06 am
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)
March 19, 2013 at 5:56 am
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
March 19, 2013 at 7:07 am
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)
March 19, 2013 at 7:23 am
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
March 20, 2013 at 9:15 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply