[SQL Server] Synchroniser entre deux instances SQL Server les vues, les fonctions utilisateurs, les procédures stockées et les types utilisateurs

  • Imprimer

DECLARE @SQL NVARCHAR(MAX)
, @ObjectName NVARCHAR(256)
, @ObjectType NVARCHAR(10)
, @Definition NVARCHAR(MAX)
, @num_lig INT

-- Temp table pour stocker les objets modifiés
DROP TABLE IF EXISTS #ModifiedObjects

CREATE TABLE #ModifiedObjects (
num_lig INT IDENTITY(1,1),
ObjectName NVARCHAR(256),
ObjectType NVARCHAR(10),
[Definition] NVARCHAR(MAX)
)

-- Récupérer les objets modifiés sur HISTO_HERA
INSERT INTO #ModifiedObjects (ObjectName, ObjectType, [Definition])
SELECT
[name] AS ObjectName,
[type] AS ObjectType,
OBJECT_DEFINITION(OBJECT_ID) AS [Definition]
FROM [sqlserver_2].[database_name].sys.objects
WHERE type IN ('V', 'FN', 'IF', 'TF', 'P') -- Vues, Fonctions, Procédures
AND modify_date > (SELECT ISNULL(MAX(modify_date), '1900-01-01')
FROM sys.objects
WHERE type = objects.type
AND name = objects.name)

-- Ajouter les types définis par l'utilisateur (TT)
INSERT INTO #ModifiedObjects (ObjectName, ObjectType, [Definition])
SELECT
name AS ObjectName,
'TT' AS ObjectType,
'CREATE TYPE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ' AS ' +
(CASE WHEN is_table_type = 1 THEN 'TABLE ' ELSE '' END) +
'(' + STUFF((SELECT ', ' + QUOTENAME(c.name) + ' ' + t.name +
CASE WHEN c.max_length = -1 THEN '(MAX)'
WHEN t.name IN ('nchar', 'nvarchar') THEN '(' + CAST(c.max_length / 2 AS NVARCHAR(10)) + ')'
WHEN t.name IN ('char', 'varchar', 'binary', 'varbinary') THEN '(' + CAST(c.max_length AS NVARCHAR(10)) + ')'
ELSE '' END
FROM [sqlserver_2].[database_name].sys.columns c
JOIN [sqlserver_2].[database_name].sys.types t ON c.user_type_id = t.user_type_id
ORDER BY c.column_id
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')'
FROM [sqlserver_2].[database_name].sys.table_types t


-- Initialisation de la boucle WHILE
SET @num_lig = 1;

WHILE EXISTS (SELECT 1 FROM #ModifiedObjects WHERE num_lig = @num_lig)
BEGIN
-- Récupérer l'objet en cours de traitement
SELECT @ObjectName = ObjectName,
@ObjectType = ObjectType,
@Definition = [Definition]
FROM #ModifiedObjects
WHERE num_lig = @num_lig

-- Supprimer l'objet s'il existe
SET @SQL = N'';

IF @ObjectType IN ('V', 'FN', 'IF', 'TF', 'P', 'TT')
BEGIN
SET @SQL = N'DROP ' +
CASE
WHEN @ObjectType = 'V' THEN 'VIEW '
WHEN @ObjectType IN ('FN', 'IF', 'TF') THEN 'FUNCTION '
WHEN @ObjectType = 'P' THEN 'PROCEDURE '
WHEN @ObjectType = 'TT' THEN 'TYPE '
END
+ QUOTENAME(@ObjectName) + N';';
END

-- suppression de l'objet s'il existe
IF @SQL <> N''
BEGIN
EXEC sp_executesql @SQL
END

-- Recréer l'objet avec sa définition mise à jour
SET @SQL = @Definition
EXEC sp_executesql @SQL

SET @num_lig = @num_lig + 1
END;

DROP TABLE IF EXISTS #ModifiedObjects