SQL Code Backup

Backup your SQL Modules into a table to keep previous versions

Execute the script below to enable the backup of your SQL Code. Backs up the following items into one table;

Stored Procedures
All Function Types
Views
Triggers

Create a job that calls the this with the database name to enable regular backups. This also works cross server so that all of your code can be stored in one place.

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
)
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