sql server version control

1
2IF OBJECT_ID('dbo.p_DBVersion') IS NULL
3  EXEC('CREATE PROCEDURE dbo.p_DBVersion AS SELECT 1')
4GO
5
6ALTER PROCEDURE dbo.p_DBVersion 
7  @NEW_VERSION char(16) = NULL
8AS
9/*
10    Nom:           p_DBVersion
11    Autor:         Miquel Martí
12    Creació:       18/03/19
13    Propòsit:      Asignar o consultar versió de la BBDD
14	Ús:	           EXEC p_DBVersion
15	               EXEC p_DBVersion '1.1'
16    
17	Historial:     18/03/19 - Miquel Martí
18                       + Creació inicial
19*/
20DECLARE @OLD_VERSION AS char(16)
21SET NOCOUNT ON 
22
23  -- consultar versió actual
24  SELECT @OLD_VERSION = CAST([value] as char(16))
25  FROM ::fn_listextendedproperty('Database Revision', default, default, default, default, default, default)
26
27  -- si no existeix, asignar-li v0.0
28  IF RTRIM(@OLD_VERSION) IS NULL 
29    SET @OLD_VERSION = '0.0'
30
31  -- si no s'especifica una nova versió, informar de l'actual
32  IF RTRIM(@NEW_VERSION) IS NULL BEGIN
33    SET @NEW_VERSION = @OLD_VERSION
34    PRINT 'Database Revision is ' + 'v'  +  RTRIM(@NEW_VERSION)
35  END
36  ELSE BEGIN-- si s'especifica, informar-ne
37    PRINT 'Upgraded Database Revision from ' + 'v' +  RTRIM(@OLD_VERSION) + ' to v' +  RTRIM(@NEW_VERSION)
38  END
39
40  -- actualitza Database Revision com a propietat avançada
41  IF EXISTS (SELECT 1 FROM fn_listextendedproperty(N'Database Revision', NULL, NULL, NULL, NULL, NULL, NULL))
42    EXEC sp_dropextendedproperty N'Database Revision', NULL, NULL, NULL, NULL, NULL, NULL
43  EXEC sp_addextendedproperty N'Database Revision', @NEW_VERSION, NULL, NULL, NULL, NULL, NULL, NULL
44GO
45