Maintain DB scripts versions executed over particular DB to avoid duplicate script executions and more.

CreateVersionHistory.sql

--- Create DBVersion Table to maintain the Versions 
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DBVersion](
	[DBVersionId] [int] NOT NULL,
	[ExecutionDate] [datetime] NOT NULL,
	[Description] [nvarchar](max) NULL,
 CONSTRAINT [PK_DBVersion] PRIMARY KEY CLUSTERED 
(
	[DBVersionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO


------------ Create Alter Scritps with proper instruction. Refer Below script example 

-- update the "set @version" and "set @description" lines
-- put your changes between the "start of changes" and "end of changes" lines
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DECLARE @newLine AS CHAR(3) = CHAR(32) + CHAR(13) + CHAR(10)
DECLARE @version INT = 388
DECLARE @description VARCHAR(max) = 'Added Foriegn key constraint for cas_mandatorylist'

IF (
		NOT (
			EXISTS (
				SELECT (1)
				FROM DBVersion
				WHERE DBVersionID = @version
				)
			)
		)
BEGIN
	PRINT 'Updating to version ' + convert(VARCHAR(10), @version) + '.'
	PRINT @description

	SET XACT_ABORT ON;

	BEGIN TRY
		BEGIN TRANSACTION

		-- START OF CHANGES
		ALTER TABLE [dbo].[cas_MandatoryLists]
			WITH CHECK ADD CONSTRAINT FK__cas_Manda__ListI__56CB4E1F FOREIGN KEY ([ListId]) REFERENCES [dbo].[cas_List]([ListId]) ON

		DELETE CASCADE

		-- END OF CHANGES
		INSERT INTO DBVersion (
			DBVersionId
			,Description
			,ExecutionDate
			)
		VALUES (
			@version
			,@description
			,GETDATE()
			)

		COMMIT TRANSACTION;
	END TRY

	BEGIN CATCH
		SELECT ERROR_NUMBER() AS ErrorNumber
			,ERROR_SEVERITY() AS ErrorSeverity
			,ERROR_STATE() AS ErrorState
			,ERROR_PROCEDURE() AS ErrorProcedure
			,ERROR_LINE() AS ErrorLine
			,ERROR_MESSAGE() AS ErrorMessage;

		-- Test XACT_STATE:
		-- If 1, the transaction is committable.
		-- If -1, the transaction is uncommittable and should 
		--     be rolled back.
		-- XACT_STATE = 0 means that there is no transaction and
		--     a commit or rollback operation would generate an error.
		-- Test whether the transaction is uncommittable.
		IF (XACT_STATE()) = - 1
		BEGIN
			PRINT N'The transaction is in an uncommittable state.' + 'Rolling back transaction.'

			ROLLBACK TRANSACTION;
		END;

		-- Test whether the transaction is committable.
		IF (XACT_STATE()) = 1
		BEGIN
			PRINT N'The transaction is committable.' + 'Committing transaction.'

			COMMIT TRANSACTION;
		END;
	END CATCH
END
ELSE
BEGIN
	PRINT 'Version ' + convert(VARCHAR(10), @version) + ' has already been run'
END
GO