Capture SQL Server Database Object Changes

By: Sebastiao Pereira | Updated: 2024-10-14 | Comments | Related: More > Comparison Data and Objects

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
               ,'&NBSP;',' ')); -- 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
               ,'&NBSP;',' ')); -- 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.

About the author

Sebastiao Pereira has over 38 years of healthcare experience, including software development expertise with databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2024-10-14