Problem
Changes in database schemas are done routinely. Is there a way to easily identify these changes?
Solution
Data Definition Language (DDL) is a group of SQL statements that can be executed to manage database objects. The idea is to create a database trigger that tracks and logs all changes to database schemas, including modifications to stored procedures, tables, views, and other schema objects and storing any event in one table.
Creating the Table
This is the table that will be used to collect all of the changes when they occur.
CREATE TABLE [dbo].[DbSchemaEvents](
[EventDate] [datetime] DEFAULT GETDATE(),
[EventType] [nvarchar](64) NULL,
[EventDDL] [nvarchar](max) NULL,
[EventXML] [xml] NULL,
[DatabaseName] [nvarchar](50) NULL,
[SchemaName] [nvarchar](50) NULL,
[ObjectName] [nvarchar](80) NULL,
[HostName] [varchar](64) NULL,
[IPAddress] [varchar](32) NULL,
[ProgramName] [nvarchar](255) NULL,
[LoginName] [nvarchar](255) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Creating the Database Trigger
This is the trigger that will fire whenever a database object is created, altered, dropped, etc. When this fires, the data will be stored in the table above.
CREATE TRIGGER [DbTrackLog]
ON DATABASE FOR
RENAME, ALTER_SCHEMA,
CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
CREATE_TABLE, ALTER_TABLE, DROP_TABLE
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 [dbo].[DbSchemaEvents]
(EventType
,EventDDL
,EventXML
,DatabaseName
,SchemaName
,ObjectName
,HostName
,IPAddress
,ProgramName
,LoginName)
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
ENABLE TRIGGER [DbTrackLog] ON DATABASE
GO
Testing the Trigger to Capture Database Changes
To test the trigger and log changes, let’s create a new user-defined function as follows.
-- =============================================
-- Author: Sebastião Pereira - MSSQLTips
-- Create date: 20240802
-- Description: Cleanup String
-- =============================================
CREATE FUNCTION [dbo].[ufnStringCleanup]
(@String nvarchar(MAX))
RETURNS nvarchar(MAX)
WITH EXECUTE AS CALLER
AS
BEGIN
IF LEN(@String) = 0
SET @String = NULL;
ELSE
SET @String = TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
@String ,' ',' ')
,CHAR(9),' ') -- tab with space
,CHAR(10),' ') -- line feed with space
,CHAR(13),' ') -- carriage return with space
,' ',' ')); -- html code for space to space
RETURN @String;
END
GO
I then will make a change to this new function, so we can see if the ALTER is also captured by the trigger.
-- =============================================
-- Author: Sebastião Pereira - MSSQLTips
-- Create date: 20240802
-- Description: Cleanup String
-- =============================================
ALTER FUNCTION [dbo].[ufnStringCleanup]
(@String nvarchar(MAX))
RETURNS nvarchar(MAX)
WITH EXECUTE AS CALLER
AS
BEGIN
IF LEN(@String) = 0
SET @String = NULL;
ELSE
SET @String = TRIM(REPLACE(REPLACE(REPLACE(REPLACE(
@String ,CHAR(9),' ') -- tab with space
,CHAR(10),' ') -- line feed with space
,CHAR(13),' ') -- carriage return with space
,' ',' ')); -- html code for space to space
WHILE @String LIKE '% %' OR @String LIKE '%( %' OR @String LIKE '% )%'
BEGIN
-- Replace double spaces with a single space
IF @String LIKE '% %'
SET @String = REPLACE(@String,' ',' ');
-- Remove space after opening parenthesis
IF @String LIKE '%( %'
SET @String = REPLACE(@String,'( ','(');
-- Remove space before closing parenthesis
IF @String LIKE '% )%'
SET @String = REPLACE(@String,' )',')');
END
RETURN @String;
END
GO
Here is an example using the user defined function:
Log of Database Changes
Let’s look at the table that was created to see if these two events: CREATE and ALTER were captured, by just selecting from this table.
We can see above both events were captured.
Comparing Before and After Code
Now that we have these events captured, we can compare the before and after to identify the changes. I will show you one way this can be done using Visual Studio Code.
Copy and paste the first EventDDL cell and save to a text file named _ToSelect.txt.
Then copy the EventDDL for the second record and save to a file named _ToCompare.txt.
Open both .txt files in Visual Studio Code.
In Visual Studio Code, click on the icon with the two pages and right-click on the _ToSelect.txt item and choose Select for Compare.
Repeat the same steps for the _ToCompare.txt file but this time choose Compare with Selected.
This will show you the difference between these two files, so you can see what was changed:
This can be useful for following up on any database change and having the ability to compare the before and after for a database object.
Next Steps
- If you want to do this for more than one database and centralize the changes in one table, change the trigger where you want to save the data and create the trigger in each database that writes to your centralized table.
- It is also possible to automate the comparison of event changes by creating a tool in Visual Studio to identify the differences.
