How to Track Stored Procedure, Function, View changes in SQL Server

Do you want to track the changes made on database objects like Stored Procedures, Views, Functions?

SQL Server supports DDL Triggers that can be used to accomplish this task.

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

They can be used to perform administrative tasks in the database such as auditing and regulating database operations like not allowing users to drop database objects etc.

1. Create A Table with required data field to track the modifications
1
2
3
4
5
6
7
8
9
10
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE TRIGGER [tr_ChangeTracking]
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION , CREATE_VIEW, ALTER_VIEW
AS
 
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
1
2
3
4
5
6
7
8
9
10
11
12
13
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

On Creating the above stored procedure, it triggers the created trigger “tr_ChangeTracking” to store the performed event i.e CREATE_PROCEDURE.

Change Tracker

 

So, it was all about How to Track Stored Procedure, Function, View changes in SQL Server, if you have any query then please comment below and let us know. If you liked this article, then please follow us on Facebook to get notification for new posts.

Happy Learning 🙂

Rahul is a Data Geek, technology enthusiast, a passionate writer, thinker with passion for computer programming.  He loves to explore technology and finds ultimate joy when writing about trending technology, geek stuff and web development.