使用 DDL 触发器备份 SQL 模块
将您的 SQL 模块备份到表中以保留以前的版本或使用触发器来跟踪或同步数据库对象的结构更改
手动或定时作业
执行以下脚本以启用 SQL 代码的备份。将以下项目备份到一个表中；
- 存储过程
- 所有函数类型
- 意见
- 触发器
创建一个使用数据库名称调用 this 的作业以启用定期备份。这也适用于跨服务器，因此您的所有代码都可以存储在一个地方。
SQL
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)GOCREATE CLUSTERED INDEX CDX_SQLModules ON [dbo].[SQLModules](ChangeDate,System,object_id)GOCREATE PROC dbo.SQLModules_Backup(@DB NVARCHAR(50),@Server NVARCHAR(50)=NULL) AS BEGINDECLARE @SQL NVARCHAR(MAX)='INSERT INTO SQLModulesSELECT '''+ISNULL(@Server+'.','')+@DB+''' System,s.name,o.name,m.object_id,GETDATE() ChangeDate,m.definitionFROM '+ISNULL(@Server+'.','')+@DB+'.sys.all_sql_modules mINNER JOIN '+ISNULL(@Server+'.','')+@DB+'.sys.all_objects o ON o.object_id=m.object_idINNER JOIN '+ISNULL(@Server+'.','')+@DB+'.sys.schemas s ON s.schema_id=o.schema_idLEFT JOIN (SELECT * FROM (SELECT *,ROW_NUMBER() OVER (PARTITION BY System,object_id ORDER BY ChangeDate DESC) RowNumberFROM SQLModules) ltWHERE 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_ASWHERE m.object_id>0 AND l.object_id IS NULL AND m.definition IS NOT NULL'EXEC sp_executesql @SQLENDGO使用 DDL 触发器
虽然手动/预定版本适用于某些人，但其他人可能需要更强大的版本。下面的代码使用 SQL Server 2005 中引入的 DDL 触发器功能，并且有一个可选的同步选项，如果需要可以跨服务器工作。我们使用了创建实用程序数据库的标准模式，但它在主数据库或自定义数据库中同样可以正常工作。
SQL
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)SQL Trigger
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 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 below--DECLARE @DB NVARCHAR(MAX)=DB_NAME(),@SQL NVARCHAR(MAX)=@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)')--EXEC {server}.{database}.dbo.SyncCode @DB,@SQL--End Optional syncronisation optionENDGOENABLE TRIGGER [Code_Watch] ON DATABASEGOSQL Sync
CREATE PROC SyncCode(@DB NVARCHAR(MAX),@SQL NVARCHAR(MAX)) AS BEGIN EXEC ('use '+@DB+'; exec sp_executesql N'''+@SQL+''' ') END