How To Track Changes In Stored Procedure, Function, And View In SQL Server

SQL Server DDL Trigger is a special kind of trigger that fires in response to Data Definition Language (DDL) statements – CREATE, ALTER, DROP (Table, Function, Index, Stored Procedures, Views.. etc).

Do you want to track the changes made on database objects, like Stored Procedures, Views, Functions? Well, SQL Server supports DDL Triggers that can be used to accomplish this task.

SQL Server DDL Trigger is a special kind of trigger that fires in response to Data Definition Language (DDL) statements – CREATE, ALTER, DROP (Table, Function, Index, Stored Procedures, Views.. etc).

They can be used to perform the administrative tasks in the database, such as - auditing and regulating database operations.

1. Create a table with the required data fields to track the modifications.
   

   CREATE TABLE [dbo].[DBChangeLog](
      [DBChangeLogID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
      [DatabaseName] [varchar](256) NOT NULL,
      [EventType] [varchar](50) NOT NULL,
      [ObjectName] [varchar](256) NOT NULL,
      [ObjectType] [varchar](25) NOT NULL,
      [SqlCommand] [varchar](max) NOT NULL,
      [EventDate] [datetime] NOT NULL,
      [LoginName] [varchar](256) NOT NULL,
   )

2. Create a DDL Trigger which tracks the changes in the DBChangeLog Table.
   

   CREATE TRIGGER [tr_ChangeTracking]ON databasefor create_procedure, alter_procedure, drop_procedure,create_table, alter_table, drop_table,create_function, alter_function, drop_function , create_view, alter_viewas
   SET nocount ON
   DECLARE @data xml
   SET @data = eventdata()
   INSERT INTO dbo.dbchangelog
               (
                           databasename,
                           eventtype,
                           objectname,
                           objecttype,
                           sqlcommand,
                           loginname,
                           eventdate
               )
               VALUES
               (
                           @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
                           @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),
                           @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)'),
                           getdate()
               )go

3. Create a Stored Procedure to test.
   

   CREATE PROCEDURE spGetAllAddress
   --Add the parameters
   for the stored procedure here
   AS
   BEGIN
   --SET NOCOUNT ON added to prevent extra result sets from
   --interfering with SELECT statements.
   SET NOCOUNT ON;
   --Insert statements
   for procedure here
   SELECT * FROM Address
   END
   GO

After creating the above mentioned stored procedure, it triggers the created trigger “tr_ChangeTracking” in order to store the performed event, i.e, CREATE_PROCEDURE.