Automatic Data Collection of Database Schema Changes in MS SQL Server

Total: 1 Average: 5

Introduction

Have you ever faced a situation when you need to make changes in a stored procedure or a view very quickly? I have, very often, especially at the implementation stage. Unfortunately, a version control system cannot help in this case. Still, how could I understand that something has been modified, and when?

This article describes a possible solution for automatic data collection about database schema changes in MS SQL Server. As usual, I will be glad to hear any alternative solutions.

Solution

  1. Create two tables: the first one will be for each database, the second one – for all databases:
    USE [DATABASE_NAME]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [srv].[ddl_log](
        [DDL_Log_GUID] [uniqueidentifier] NOT NULL,
        [PostTime] [datetime] NOT NULL,
        [DB_Login] [nvarchar](255) NULL,
        [DB_User] [nvarchar](255) NULL,
        [Event] [nvarchar](255) NULL,
        [TSQL] [nvarchar](max) NULL,
     CONSTRAINT [PK_ddl_log] PRIMARY KEY CLUSTERED 
    (
        [DDL_Log_GUID] 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]
    
    GO
    
    ALTER TABLE [srv].[ddl_log] ADD  CONSTRAINT [DF_ddl_log_DDL_Log_GUID]  DEFAULT (newid()) FOR [DDL_Log_GUID]
    GO
    
    USE [DATABASE_NAME]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [srv].[ddl_log_all](
        [DDL_Log_GUID] [uniqueidentifier] NOT NULL,
        [Server_Name] [nvarchar](255) NOT NULL,
        [DB_Name] [nvarchar](255) NOT NULL,
        [PostTime] [datetime] NOT NULL,
        [DB_Login] [nvarchar](255) NULL,
        [DB_User] [nvarchar](255) NULL,
        [Event] [nvarchar](255) NULL,
        [TSQL] [nvarchar](max) NULL,
        [InsertUTCDate] [datetime] NOT NULL,
     CONSTRAINT [PK_ddl_log_all] PRIMARY KEY CLUSTERED 
    (
        [DDL_Log_GUID] 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]
    
    GO
    
    ALTER TABLE [srv].[ddl_log_all] ADD  CONSTRAINT [DF_ddl_log_all_DDL_Log_GUID]  DEFAULT (newid()) FOR [DDL_Log_GUID]
    GO
    
    ALTER TABLE [srv].[ddl_log_all] ADD  CONSTRAINT [DF_ddl_log_all_InsertUTCDate]  DEFAULT (getutcdate()) FOR [InsertUTCDate]
    GO
  2. Create a DDL-trigger for a database which collects scheme changes:
    USE [DATABASE_NAME]
    GO
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TRIGGER [SchemaLog] 
    ON DATABASE --ALL SERVER 
    FOR DDL_DATABASE_LEVEL_EVENTS 
    AS
        SET NOCOUNT ON;
        SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
        DECLARE @data XML
        begin try
        if(CURRENT_USER<>'NT AUTHORITY\NETWORK SERVICE' and SYSTEM_USER<>'NT AUTHORITY\NETWORK SERVICE')
        begin
            SET @data = EVENTDATA();
            INSERT srv.ddl_log(
                        PostTime,
                        DB_Login,
                        DB_User,
                        Event,
                        TSQL
                      ) 
            select 
                        GETUTCDATE(),
                        CONVERT(nvarchar(255), SYSTEM_USER),
                        CONVERT(nvarchar(255), CURRENT_USER), 
                        @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(255)'), 
                        @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')
            where       @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(255)') not in('UPDATE_STATISTICS', 'ALTER_INDEX')
                    and @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)') not like '%Msmerge%'; 
                        --there is no need in tracking changes of replication objects
        end
        end try
        begin catch
        end catch
    
    GO
    
    SET ANSI_NULLS OFF
    GO
    
    SET QUOTED_IDENTIFIER OFF
    GO
    
    ENABLE TRIGGER [SchemaLog] ON DATABASE
    GO

I recommend adjusting a filter and not making a DDL-trigger for the whole server. It is useless, as you will get a lot of needless information. In this case, it is better to create a trigger for each database.
However, you will have to turn off this trigger during complicated operations, for example, replication. But later, you will be able to turn it on again.

  1.  You will need to gather information in a single table. For example, you can do it with a task in the SQL Server Agent one time a week.
  2. It is possible to gather everything in one table by another way you prefer.

Additionally, I recommend deleting old data.

Result

In this article, I have analyzed an example of implementing an automatic data collection about changes of databases schemes in MS SQL Server. It allows us to find out what and when has been modified, and, if necessary, to revert them. In general, this solution may be helpful at the implementation stage where there are a lot of mistakes and when we have different versions of databases copies to be analyzed. If you wish to find out a reason for changes, you can do it by retrieving a revision history.

Also read:

Automatic Data Collection about Completed Tasks in MS SQL Server

Evgeniy Gribkov