CREATE TABLE dbo.SQLModules( [System] varchar(50) NOT NULL, [Schema] nvarchar(50) NULL, ObjectName nvarchar(200) NULL, [object_id] int NOT NULL, ChangeDate datetime NULL, [definition] nvarchar(max) NOT NULL) GO CREATE CLUSTERED INDEX CDX_SQLModules ON [Utilities].[dbo].[SQLModules](ChangeDate,System,object_id) GO CREATE PROC dbo.SQLModules_Backup(@DB NVARCHAR(50),@Server NVARCHAR(50)=NULL) AS BEGIN DECLARE @SQL NVARCHAR(MAX)='INSERT INTO SQLModules SELECT '''+ISNULL(@Server+'.','')+@DB+''' System,s.name,o.name, m.object_id, GETDATE() ChangeDate, m.definition FROM '+ISNULL(@Server+'.','')+@DB+'.sys.all_sql_modules m INNER JOIN '+ISNULL(@Server+'.','')+@DB+'.sys.all_objects o ON o.object_id=m.object_id INNER JOIN '+ISNULL(@Server+'.','')+@DB+'.sys.schemas s ON s.schema_id=o.schema_id LEFT JOIN ( SELECT * FROM ( SELECT *,ROW_NUMBER() OVER (PARTITION BY System,object_id ORDER BY ChangeDate DESC) RowNumber FROM SQLModules) lt WHERE RowNumber=1) l ON l.object_id=m.object_id AND l.System='''+ISNULL(@Server+'.','')+@DB+''' AND m.definition COLLATE Latin1_General_CI_AS=l.definition COLLATE Latin1_General_CI_AS WHERE m.object_id>0 AND l.object_id IS NULL AND m.definition IS NOT NULL' EXEC sp_executesql @SQL END GO
While the manual/scheduled version works for some, others may need a more robust version. The code below uses the DDL trigger functionality introduced in SQL Server 2005, and has an optional synchronisation option that works across server if required. We have used our standard pattern of creating a utilities database, but it can work equally well in the master or custom databases.
CREATE TABLE [dbo].[DDLEvents]( [EventDate] [datetime] NOT NULL DEFAULT (getutcdate()), [EventType] [nvarchar](64) NULL, [EventDDL] [nvarchar](max) NULL, [DatabaseName] [nvarchar](255) NULL, [SchemaName] [nvarchar](255) NULL, [ObjectName] [nvarchar](255) NULL, [HostName] [varchar](64) NULL, [IPAddress] [varchar](32) NULL, [ProgramName] [nvarchar](255) NULL, [LoginName] [nvarchar](255) NULL)
CREATE TRIGGER [Code_Watch] ON DATABASEFOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_TABLE, ALTER_TABLE, DROP_TABLE, CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION, CREATE_INDEX, ALTER_INDEX, DROP_INDEX, CREATE_SCHEMA, DROP_SCHEMAAS BEGINSET ANSI_NULLS ON;SET ANSI_PADDING ON;SET ANSI_WARNINGS ON;SET ARITHABORT ON;SET CONCAT_NULL_YIELDS_NULL ON;SET NUMERIC_ROUNDABORT OFF;SET QUOTED_IDENTIFIER ON;DECLARE @EventData XML = EVENTDATA();DECLARE @ip VARCHAR(32) = (SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID);INSERT INTO Utilities.dbo.DDLEvents(EventType,EventDDL,DatabaseName,SchemaName,ObjectName,HostName,IPAddress,ProgramName,LoginName)SELECT@EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),DB_NAME(),@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)'),@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)'),HOST_NAME(),@ip,PROGRAM_NAME(),SUSER_SNAME();--Optional syncronisation option, see belowDECLARE @DB NVARCHAR(MAX)=DB_NAME(),@SQL NVARCHAR(MAX)=@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)')EXEC [Server].Utilities.dbo.SyncCode @DB,@SQL--End Optional syncronisation optionENDGOENABLE TRIGGER [Code_Watch] ON DATABASEGO
ALTER PROC SyncCode(@DB NVARCHAR(MAX),@SQL NVARCHAR(MAX)) AS BEGIN EXEC ('use '+@DB+'; exec sp_executesql N'''+@SQL+''' ') END