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](max) NOT 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.

output