Auditing changes to database source code can be easily implemented with a database trigger. Here’s a simple implementation of stored procedure auditing using an audit table and a database trigger. It assumes that a schema named Audit already exists.
CREATE TABLE Audit.AuditStoredProcedures (
, ObjectName sysname
, LoginName sysname
, ChangeDate datetime
, EventType sysname
, EventDataXml xml
Notice the EventDataXml column. Using an nvarchar column to store the source text of the stored procedure isn't satisfactory because a stored procedure can be many times larger than the maximum size of an nvarchar column. Although using nvarchar(MAX) might not provide enough space in theory, practically speaking it will. But reading the code inserted into a nvarchar(MAX) column isn't convenient at all. It ends up as one very long, unformatted string. The xml data type leaves the source code appearing like (although not exactly like, more on that later) it did when it was written to the audit table.
It can be argued that the first five columns in the table are superfluous because they exist in the complete XML stored in the EventDataXml column. Because the first five columns contain key information useful for searching, the redundancy is justified for the sake of convenience. It's far easier to read and search simple string values stored in character columns than obtain them from the EventDataXml column.
CREATE TRIGGER dbtAuditStoredProcedures
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
DECLARE @eventdata XML;
SET @eventdata = EVENTDATA();
INSERT INTO Audit.AuditStoredProcedures (DatabaseName,ObjectName,LoginName,ChangeDate,EventType,EventDataXml)
, @eventdata.value('(/EVENT_INSTANCE/ObjectName)', 'sysname')
, @eventdata.value('(/EVENT_INSTANCE/LoginName)', 'sysname')
, @eventdata.value('(/EVENT_INSTANCE/EventType)', 'sysname')
The database trigger fires after the fact. When a stored procedure is dropped, there is no longer any code for the stored procedure and the EventDataXml column won't contain the code that was there before the drop. Similarly, an ALTER statement will store the stored procedure code after the change, not before the change. If this auditing scheme is implemented before any stored procedures are created and a complete history is maintained in the audit table, there would be a complete record of the entire life of a stored procedure. If the database trigger is implemented where there are preexisting stored procedures, you'd need to run the ALTER statement (but not actually change anything) on all existing stored procedures to get them logged into the audit table. If you don't do this and a change is made, you won't have a record of the original state of the code.
When the code is stored, the database trigger stores it as XML. Minor formatting changes will occur due to the encoding of special characters. For example, a > sign gets encoded as > according to standard XML formatting rules. You'll be able to figure out what changed, but restoring stored procedure code from the XML will be inconvenient because of this.
As you can see, auditing is not a replacement for source code version control. You may be able to get by with a simplistic auditing process in an environment where the body of code is relatively small and stable. Nor are database backups suitable replacements for version control. Even if you have transaction log backups and can do point in time recovery, tracking code changes by restoring from backups is inconvenient at best and just not feasible at worst.