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

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.
    1. CREATE TABLE [dbo].[DBChangeLog](    
    2.    [DBChangeLogID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,    
    3.    [DatabaseName] [varchar](256) NOT NULL,    
    4.    [EventType] [varchar](50) NOT NULL,    
    5.    [ObjectName] [varchar](256) NOT NULL,    
    6.    [ObjectType] [varchar](25) NOT NULL,    
    7.    [SqlCommand] [varchar](maxNOT NULL,    
    8.    [EventDate] [datetime] NOT NULL,    
    9.    [LoginName] [varchar](256) NOT NULL,    
    10. )    
  1. Create a DDL Trigger which tracks the changes in the DBChangeLog Table.
    1. 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  
    2. SET nocount ON   
    3. DECLARE @data xml   
    4. SET @data = eventdata()   
    5. INSERT INTO dbo.dbchangelog   
    6.             (   
    7.                         databasename,   
    8.                         eventtype,   
    9.                         objectname,   
    10.                         objecttype,   
    11.                         sqlcommand,   
    12.                         loginname,   
    13.                         eventdate   
    14.             )   
    15.             VALUES   
    16.             (   
    17.                         @data.value('(/EVENT_INSTANCE/DatabaseName)[1]''varchar(256)'),   
    18.                         @data.value('(/EVENT_INSTANCE/EventType)[1]''varchar(50)'),   
    19.                         @data.value('(/EVENT_INSTANCE/ObjectName)[1]''varchar(256)'),   
    20.                         @data.value('(/EVENT_INSTANCE/ObjectType)[1]''varchar(25)'),   
    21.                         @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]''varchar(max)'),   
    22.                         @data.value('(/EVENT_INSTANCE/LoginName)[1]''varchar(256)'),   
    23.                         getdate()   
    24.             )go  
  1. Create a Stored Procedure to test.
    1. CREATE PROCEDURE spGetAllAddress  
    2. --Add the parameters  
    3. for the stored procedure here  
    4. AS  
    5. BEGIN  
    6. --SET NOCOUNT ON added to prevent extra result sets from  
    7. --interfering with SELECT statements.  
    8. SET NOCOUNT ON;  
    9. --Insert statements  
    10. for procedure here  
    11. SELECT * FROM Address  
    12. END  
    13. 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.