Backup del codice SQL
- Procedura di archiviazione
- Tutti i tipi di funzione
- Visualizzazioni
- trigger
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