version control sql server scripts code example
Example: sql server version control
IF OBJECT_ID('dbo.p_DBVersion') IS NULL
EXEC('CREATE PROCEDURE dbo.p_DBVersion AS SELECT 1')
GO
ALTER PROCEDURE dbo.p_DBVersion
@NEW_VERSION char(16) = NULL
AS
DECLARE @OLD_VERSION AS char(16)
SET NOCOUNT ON
SELECT @OLD_VERSION = CAST([value] as char(16))
FROM ::fn_listextendedproperty('Database Revision', default, default, default, default, default, default)
IF RTRIM(@OLD_VERSION) IS NULL
SET @OLD_VERSION = '0.0'
IF RTRIM(@NEW_VERSION) IS NULL BEGIN
SET @NEW_VERSION = @OLD_VERSION
PRINT 'Database Revision is ' + 'v' + RTRIM(@NEW_VERSION)
END
ELSE BEGIN
PRINT 'Upgraded Database Revision from ' + 'v' + RTRIM(@OLD_VERSION) + ' to v' + RTRIM(@NEW_VERSION)
END
IF EXISTS (SELECT 1 FROM fn_listextendedproperty(N'Database Revision', NULL, NULL, NULL, NULL, NULL, NULL))
EXEC sp_dropextendedproperty N'Database Revision', NULL, NULL, NULL, NULL, NULL, NULL
EXEC sp_addextendedproperty N'Database Revision', @NEW_VERSION, NULL, NULL, NULL, NULL, NULL, NULL
GO