Visual Studio Database Project Migrations Help Scripts

README.MD

This was used successfully on Visual Studio 2015 Professional on a Windows 10 laptop to deploy changes to SQL Azure V12.

I hope it will work for you as well. :)

First create the Migrations.sql table and the fnMigrationExists.sql helper functions and stored procedures.

Then edit your project properties, go to Build Events and paste the Pre-Build event command line into the window.

This windows batch script will check the migrations folders and dynamically build Pre and Post migration files for you automatically.

Create a Migrations folder in your DB Project.

In the Migrations Folder, Create a Post folder, a Pre folder, as well as Script.PostDeployment.sql and Script.PreDeployment.sql.

Make sure BuildAction is set to PostDeploy and PreDeploy respectively.

Now you just have to place your migrations as .sql files in the Pre and Post folders and the build script will automagically pull the sql files from the folders in and create Pre and Post deployment scripts for you.

Your folder structure should look like this:

Project
├── Functions/
│   ├── fnMigrationExists.sql
├── Migrations/
│   ├── Post/
│   │   ├── 001 - My First Post Migration.sql
│   ├── Pre/
│   │   ├── 001 - My First Pre Migration.sql
│   ├── Script.PostDeployment.sql
│   ├── Script.PreDeployment.sql
├── Security/
├── Stored Procedures/
├── Tables/
│   ├── Migrations.sql
├── Views/

Important: Make sure you add your migrations as Build Action: None and Do not copy!

Here is a small example of the log when you deploy the project with migrations:

Skipped: Post-Deployment Migration Post\007 - Remove LegacyTable1 data and table.sql - already applied
Skipped: Post-Deployment Migration Post\008 - Remove LegacyTable2 data and table.sql - already applied
Executing: Post-Deployment Migration Post\009 - Remove SettingHistoryType 4 Status.sql
HistoryTypes has entry with Id 4 and Name Status - removing
HistoryTypes entry for Status removed.
Success: Post-Deployment Migration Post\009 - Remove SettingHistoryType 4 Status.sql applied
Executing: Post-Deployment Migration Post\010 - Remove unused Substatus 10 Save.sql
Status has entry with Id 10 and Name Save - removing
Status entry for Save removed.
Success: Post-Deployment Migration Post\010 - Remove unused Substatus 10 Save.sql applied

Here is small example of the generated Script.PreDeployment.sql:

--Generated By Pre-build event command line tool 
DECLARE @migration_status int = dbo.fnMigrationExists('Pre\105 - ZipCodes column nullability changes.sql', '00 c2 f2 47 68 1b 8d 82 30 b4 b1 7b 50 9a 25 82'); 
if(@migration_status = 0) 
BEGIN 
   SET XACT_ABORT ON 
   begin transaction 
   PRINT N'Executing: Pre-Deployment Migration Pre\105 - ZipCodes column nullability changes.sql'; 
   :r ".\Pre\105 - ZipCodes column nullability changes.sql" 
   INSERT INTO [dbo].[Migrations] ([Filename], [Hash]) VALUES ('Pre\105 - ZipCodes column nullability changes.sql', '00 c2 f2 47 68 1b 8d 82 30 b4 b1 7b 50 9a 25 82') 
   commit transaction 
   PRINT N'Success: Pre-Deployment Migration Pre\105 - ZipCodes column nullability changes.sql applied'; 
END 
if(@migration_status = 1) 
BEGIN 
   PRINT N'Skipped: Pre-Deployment Migration Pre\105 - ZipCodes column nullability changes.sql - already applied' 
END 
if(@migration_status = 2) 
BEGIN 
   PRINT N'Error: Pre-Deployment Migration Pre\105 - ZipCodes column nullability changes.sql hash mismatch' 
   RAISERROR(50001, 10, 127) 
END 
GO 
DECLARE @migration_status int = dbo.fnMigrationExists('Pre\106 - Remove Carriers LineHaulTruckCost column.sql', 'e5 c3 8d 90 92 10 82 a0 07 3b 08 72 d2 a1 6b 0d'); 
if(@migration_status = 0) 
BEGIN 
   SET XACT_ABORT ON 
   begin transaction 
   PRINT N'Executing: Pre-Deployment Migration Pre\106 - Remove Carriers LineHaulTruckCost column.sql'; 
   :r ".\Pre\106 - Remove Carriers LineHaulTruckCost column.sql" 
   INSERT INTO [dbo].[Migrations] ([Filename], [Hash]) VALUES ('Pre\106 - Remove Carriers LineHaulTruckCost column.sql', 'e5 c3 8d 90 92 10 82 a0 07 3b 08 72 d2 a1 6b 0d') 
   commit transaction 
   PRINT N'Success: Pre-Deployment Migration Pre\106 - Remove Carriers LineHaulTruckCost column.sql applied'; 
END 
if(@migration_status = 1) 
BEGIN 
   PRINT N'Skipped: Pre-Deployment Migration Pre\106 - Remove Carriers LineHaulTruckCost column.sql - already applied' 
END 
if(@migration_status = 2) 
BEGIN 
   PRINT N'Error: Pre-Deployment Migration Pre\106 - Remove Carriers LineHaulTruckCost column.sql hash mismatch' 
   RAISERROR(50001, 10, 127) 
END 
GO 

Finally, as part of this project I had to re-evaluate our nullable columns. I attached a linqpad script (Nullability.cs).

It produces output like this:

// TableName	ColumnName	ColumnString	DefaultValue	NullCount	DefaultCount	NotNullOrDefaultCount
// CityRecord	Elevation	INT	0	71796	35635	2096528
// CityRecord	FeatureClass	CHAR	''	2704	0	2205745
// Coupon	MaxUses	INT	0	3	1	5
// User	UserName	NVARCHAR(256)	''	1	1	500

The counts are there to help you make a informed decision on whether nullability is used.

The DefaultValue column is meant to be manually adjusted to whatever default you need.

I then copy the first 4 columns ( TableName, ColumnName, ColumnString and DefaultValue ) into my Sublime Text and run RegEx on it. Here is a visual representation of the regex I use: https://regex101.com/r/cD9tV1/3

Input:

CityRecord	Elevation	INT	0
CityRecord	FeatureClass	CHAR	''
Coupon	MaxUses	INT	0
User	UserName	NVARCHAR(256)	''

Search: ^(?'Table'\w*)\s+(?'Column'\w*)\s+(?'Type'\w*(?:\((?:\w*|\d*\, \d*)\))?)\s+(?'Default'[\w\'\? \X\(\)\.]*?)$

Replace:

NOT NULL CONSTRAINT [DF_$+{Table}_$+{Column}] DEFAULT (($+{Default}))
If dbo.fnTableExists('$+{Table}') = 1 AND dbo.fnColumnExists('$+{Table}', '$+{Column}') = 1
Begin
    DECLARE @$+{Table}_$+{Column}_def $+{Type} = $+{Default};

    PRINT N'$+{Table} exists and $+{Column} column exists. Defaulting data to '+TRY_CAST(@$+{Table}_$+{Column}_def AS NVARCHAR(MAX))+'.';
    exec [dbo].[sp_DropIndicesOnColumn] '$+{Table}', '$+{Column}';
    UPDATE [dbo].[$+{Table}] SET [$+{Column}] = @$+{Table}_$+{Column}_def WHERE [$+{Column}] IS NULL;
    ALTER TABLE [dbo].[$+{Table}] ALTER COLUMN [$+{Column}] $+{Type} NOT NULL;
    PRINT N'$+{Column} column has been defaulted to '+TRY_CAST(@$+{Table}_$+{Column}_def AS NVARCHAR(MAX))+' and set to not null.';
End

Result:

NOT NULL CONSTRAINT [DF_CityRecord_Elevation] DEFAULT ((0))
If dbo.fnTableExists('CityRecord') = 1 AND dbo.fnColumnExists('CityRecord', 'Elevation') = 1
Begin
    DECLARE @CityRecord_Elevation_def INT = 0;

    PRINT N'CityRecord exists and Elevation column exists. Defaulting data to '+TRY_CAST(@CityRecord_Elevation_def AS NVARCHAR(MAX))+'.';
    exec [dbo].[sp_DropIndicesOnColumn] 'CityRecord', 'Elevation';
    UPDATE [dbo].[CityRecord] SET [Elevation] = @CityRecord_Elevation_def WHERE [Elevation] IS NULL;
    ALTER TABLE [dbo].[CityRecord] ALTER COLUMN [Elevation] INT NOT NULL;
    PRINT N'Elevation column has been defaulted to '+TRY_CAST(@CityRecord_Elevation_def AS NVARCHAR(MAX))+' and set to not null.';
End

NOT NULL CONSTRAINT [DF_CityRecord_FeatureClass] DEFAULT ((''))
If dbo.fnTableExists('CityRecord') = 1 AND dbo.fnColumnExists('CityRecord', 'FeatureClass') = 1
Begin
    DECLARE @CityRecord_FeatureClass_def CHAR = '';

    PRINT N'CityRecord exists and FeatureClass column exists. Defaulting data to '+TRY_CAST(@CityRecord_FeatureClass_def AS NVARCHAR(MAX))+'.';
    exec [dbo].[sp_DropIndicesOnColumn] 'CityRecord', 'FeatureClass';
    UPDATE [dbo].[CityRecord] SET [FeatureClass] = @CityRecord_FeatureClass_def WHERE [FeatureClass] IS NULL;
    ALTER TABLE [dbo].[CityRecord] ALTER COLUMN [FeatureClass] CHAR NOT NULL;
    PRINT N'FeatureClass column has been defaulted to '+TRY_CAST(@CityRecord_FeatureClass_def AS NVARCHAR(MAX))+' and set to not null.';
End

NOT NULL CONSTRAINT [DF_Coupon_MaxUses] DEFAULT ((0))
If dbo.fnTableExists('Coupon') = 1 AND dbo.fnColumnExists('Coupon', 'MaxUses') = 1
Begin
    DECLARE @Coupon_MaxUses_def INT = 0;

    PRINT N'Coupon exists and MaxUses column exists. Defaulting data to '+TRY_CAST(@Coupon_MaxUses_def AS NVARCHAR(MAX))+'.';
    exec [dbo].[sp_DropIndicesOnColumn] 'Coupon', 'MaxUses';
    UPDATE [dbo].[Coupon] SET [MaxUses] = @Coupon_MaxUses_def WHERE [MaxUses] IS NULL;
    ALTER TABLE [dbo].[Coupon] ALTER COLUMN [MaxUses] INT NOT NULL;
    PRINT N'MaxUses column has been defaulted to '+TRY_CAST(@Coupon_MaxUses_def AS NVARCHAR(MAX))+' and set to not null.';
End

NOT NULL CONSTRAINT [DF_User_UserName] DEFAULT ((''))
If dbo.fnTableExists('User') = 1 AND dbo.fnColumnExists('User', 'UserName') = 1
Begin
    DECLARE @User_UserName_def NVARCHAR(256) = '';

    PRINT N'User exists and UserName column exists. Defaulting data to '+TRY_CAST(@User_UserName_def AS NVARCHAR(MAX))+'.';
    exec [dbo].[sp_DropIndicesOnColumn] 'User', 'UserName';
    UPDATE [dbo].[User] SET [UserName] = @User_UserName_def WHERE [UserName] IS NULL;
    ALTER TABLE [dbo].[User] ALTER COLUMN [UserName] NVARCHAR(256) NOT NULL;
    PRINT N'UserName column has been defaulted to '+TRY_CAST(@User_UserName_def AS NVARCHAR(MAX))+' and set to not null.';
End

I cut out the first line over each block and use that to replace the NULL in the table definition. The rest goes into a migration file.

Hope that helps!

Example Migration.sql

If dbo.fnTableExists('Rewards') = 1 AND dbo.fnColumnExists('Rewards', 'Completed') = 1
Begin
	PRINT N'Rewards exists and Completed column exists. Removing column.';
	ALTER TABLE [dbo].[Rewards] DROP COLUMN [Completed];
	PRINT N'Completed column has been removed.';
End

fnMigrationExists.sql

Create Function [dbo].[fnMigrationExists](@file_name nvarchar(max), @hash nvarchar(max))
Returns int 
Begin 
    Return 
    (
		SELECT Cast(
		    CASE 
		      WHEN NOT EXISTS(SELECT NULL AS [EMPTY] FROM [Migrations] WHERE [Filename] = @file_name) THEN 0
		      WHEN EXISTS(SELECT NULL AS [EMPTY] FROM [Migrations] WHERE [Filename] = @file_name AND [Hash] = @hash) THEN 1
		      ELSE 2
		    END
		 as Int)
    );
End

Helpful Functions.sql

Create Function [dbo].[fnColumnExists]
(
	@table_name nvarchar(max),
	@column_name nvarchar(max)
)
Returns bit 
Begin 
    Return (Select Cast(Count(*) as Bit) From INFORMATION_SCHEMA.COLUMNS Where TABLE_SCHEMA = SCHEMA_NAME() And TABLE_NAME = @table_name And COLUMN_NAME = @column_name);
End


Create Function [dbo].[fnConstraintExists]
(
	@table_name nvarchar(max),
	@constraint_name nvarchar(max)
)
Returns bit 
Begin 
    Return (Select Cast(Count(*) as Bit) From INFORMATION_SCHEMA.TABLE_CONSTRAINTS Where TABLE_SCHEMA = SCHEMA_NAME() And TABLE_NAME = @table_name And CONSTRAINT_NAME = @constraint_name);
End


Create Function [dbo].[fnDefaultConstraintExists]
(
	@table_name nvarchar(max),
	@column_name nvarchar(max),
	@default_constraint_name nvarchar(max)
)
Returns bit 
Begin 
	Return (
	Select Cast(Count(*) as Bit)
	FROM 
		sys.all_columns
	  INNER JOIN
		sys.tables
		ON all_columns.object_id = tables.object_id
	  INNER JOIN 
		sys.schemas
		ON tables.schema_id = schemas.schema_id
	  INNER JOIN
		sys.default_constraints
		ON all_columns.default_object_id = default_constraints.object_id
	WHERE 
		schemas.name = SCHEMA_NAME()
		AND tables.name = @table_name
		AND all_columns.name = @column_name
		AND default_constraints.name = @default_constraint_name
	);
End


Create Function [dbo].[fnFunctionExists]
(
	@function_name nvarchar(max)
)
Returns bit 
Begin 
    Return (Select Cast(Count(*) as Bit) From INFORMATION_SCHEMA.ROUTINES Where SPECIFIC_SCHEMA = SCHEMA_NAME() And SPECIFIC_NAME = @function_name And ROUTINE_TYPE = 'FUNCTION');
End


Create Function [dbo].[fnIndexExists]
(
	@table_name nvarchar(max), 
	@index_name nvarchar(max)
)
Returns bit 
Begin 
    Return (Select Cast(Count(*) as Bit) From sys.indexes Where object_id = Object_Id(@table_name) AND name = @index_name);
End


Create Function [dbo].[fnStoredProcedureExists]
(
	@sp_name nvarchar(max)
)
Returns bit 
Begin 
    Return (Select Cast(Count(*) as Bit) From INFORMATION_SCHEMA.ROUTINES Where SPECIFIC_SCHEMA = SCHEMA_NAME() And SPECIFIC_NAME = @sp_name And ROUTINE_TYPE = 'PROCEDURE');
End


Create Function [dbo].[fnTableExists]
(
	@table_name nvarchar(max)
)
Returns bit 
Begin 
    Return (Select Cast(Count(*) as Bit) From INFORMATION_SCHEMA.TABLES Where TABLE_SCHEMA = SCHEMA_NAME() And TABLE_NAME = @table_name);
End


Create Function [dbo].[fnTableHasData]
(
	@table_name nvarchar(max)
)
Returns bit 
Begin 
    Return (Select TOP 1 Cast(row_count as Bit) FROM sys.dm_db_partition_stats WHERE Object_Name(object_id) = @table_name ORDER BY row_count DESC);
End


Create Function [dbo].[fnViewExists]
(
	@view_name nvarchar(max)
)
Returns bit 
Begin 
    Return (Select Cast(Count(*) as Bit) From INFORMATION_SCHEMA.VIEWS Where TABLE_SCHEMA = SCHEMA_NAME() And TABLE_NAME = @view_name);
End

Helpful Stored Procedures.sql

CREATE PROCEDURE sp_DropDefaultsOnColumn
	@table_name nvarchar(max),
	@column_name nvarchar(max)
AS
BEGIN
  DECLARE @sql AS VARCHAR(MAX)='';

  SELECT @sql = @sql + 
    'ALTER TABLE [' + SCHEMA_NAME() + '].[' + @table_name + '] DROP CONSTRAINT [' + d.name + '];' +CHAR(13)+CHAR(10)
  FROM sys.default_constraints d       
  WHERE 
  d.parent_object_id = OBJECT_ID(@table_name) 
  AND COL_NAME(d.parent_object_id,d.parent_column_id) = @column_name;

  EXEC(@sql);
END


CREATE PROCEDURE sp_DropIndicesOnColumn
	@table_name nvarchar(max),
	@column_name nvarchar(max)
AS
BEGIN
  DECLARE @sql AS VARCHAR(MAX)='';
  
  SELECT @sql = @sql + 
  'DROP INDEX [' + i.name + '] ON [' + SCHEMA_NAME() + '].[' + @table_name + '];' +CHAR(13)+CHAR(10)
  FROM 
    sys.indexes AS i
  INNER JOIN sys.index_columns AS ic   
    ON i.object_id = ic.object_id AND i.index_id = ic.index_id  
  WHERE 
    i.object_id = OBJECT_ID(@table_name)
    AND COL_NAME(ic.object_id,ic.column_id) = @column_name;
  
  EXEC(@sql);
END

Migrations.sql

CREATE TABLE [dbo].[Migrations] (
    [Id]            INT            NOT NULL IDENTITY (1, 1) CONSTRAINT [PK_Migrations] PRIMARY KEY,
    [Filename]      NVARCHAR (500) NOT NULL,
    [Hash]          NVARCHAR (50)  NOT NULL,
    [ExecutionDate] DATETIME2 (7)  NOT NULL CONSTRAINT [DF_Migrations_ExecutionDate] DEFAULT (sysutcdatetime()),
);

GO CREATE UNIQUE INDEX [IX_Migrations_Filename] ON [dbo].[Migrations] ([Filename]);

Nullability.cs

// Needs Dapper installed from NuGet!
// Depending on the amount of nullable columns and the size of your tables, this can take a long time!
// I recommend running this on a replica, not directly on your production DB as it put long lasting locks on your tables.
// Output looks like this:
// TableName	ColumnName	ColumnString	DefaultValue	NullCount	DefaultCount	NotNullOrDefaultCount
// CityRecord	Elevation	INT	0	71796	35635	2096528
// CityRecord	FeatureClass	CHAR	''	2704	0	2205745
// Coupon	MaxUses	INT	0	28	2	6535880
void Main()
{
  var types = sys.Types.ToDictionary(t => t.User_type_id, t => t.Name);
  
  sys.All_objects
  .Where(t => t.Type == "U" && t.Schema_id == 1)
  .Join(sys.All_columns.Where(c => c.Is_nullable.HasValue && c.Is_nullable.Value),
   tbls => tbls.Object_id,
   clmns => clmns.Object_id,
   (tbls, clmns) => new
   {
      TableName = tbls.Name,
      ColumnName = clmns.Name,
      ColumnMaxLength = clmns.Max_length,
      ColumnPrecision = clmns.Precision,
      ColumnScale = clmns.Scale,
      User_Type_Id = clmns.User_type_id
   }
   )
   .ToList()
   .Where(a => types[a.User_Type_Id] != "ntext" && types[a.User_Type_Id] != "text") // ntext/text doesn't work
    .Select(a => new
    {
      a.TableName,
      a.ColumnName,
      Type = types[a.User_Type_Id],
      MaxLength = a.ColumnMaxLength,
      Precision = a.ColumnPrecision,
      Scale = a.ColumnScale,
      Counts = GetCount(Connection, a.TableName, a.ColumnName, types[a.User_Type_Id])
    })
    .Select(a => new 
    {
      a.TableName,
      a.ColumnName,
      ColumnString = GetColumnTypeString(a.Type, a.MaxLength, a.Precision, a.Scale),
      DefaultValue = a.Counts.DefaultValue,
      NullCount = a.Counts.NullCount,
      DefaultCount = a.Counts.DefaultCount,
      NotNullOrDefaultCount = a.Counts.NotNullOrDefaultCount
    }
    )
    .OrderBy(a => a.TableName)
    .ThenBy(a => a.ColumnName)
   .Dump();
}

public static Counts GetCount(IDbConnection connection,string tableName, string columnName, string type)
{
  var baseQuery = $"SELECT Count(*) FROM [{tableName}] WHERE ";
  var nullQuery = baseQuery + $"[{columnName}] is null";
  var defaultQuery = baseQuery + $"[{columnName}] is not null";
  var notNullOrDefaultQuery = defaultQuery;
  string defaultValue = "";
  
  switch (type)
  {
    case "varchar":
    case "nvarchar":
    case "text":
    case "uniqueidentifier":
    case "char":
    case "ntext":
      defaultQuery = defaultQuery + $" and LEN([{columnName}]) = 0";
      notNullOrDefaultQuery = notNullOrDefaultQuery + $" and LEN([{columnName}]) > 0";
      defaultValue = "''";
      break;
      
    case "int":
    case "decimal":
    case "bit":
    case "tinyint":
    case "bigint":
    case "float":
    case "smallint":
      defaultQuery = defaultQuery + $" and [{columnName}] = 0";
      notNullOrDefaultQuery = notNullOrDefaultQuery + $" and [{columnName}] > 0";
      defaultValue = "0";
    break;

    case "datetime":
      defaultQuery = defaultQuery + $" and [{columnName}] = CAST('01-01-1900 00:00:00' as datetime)";
      notNullOrDefaultQuery = notNullOrDefaultQuery +  $" and [{columnName}] > CAST('01-01-1900 00:00:00' as datetime)";
      defaultValue = "01-01-1900 00:00:00";
    break;

    case "datetime2":
      defaultQuery = defaultQuery + $" and [{columnName}] = CAST('01-01-0001 00:00:00' as datetime2)";
      notNullOrDefaultQuery = notNullOrDefaultQuery + $" and [{columnName}] > CAST('01-01-0001 00:00:00' as datetime2)";
      defaultValue = "01-01-0001 00:00:00";
    break;
  }

  return new Counts(
    connection.ExecuteScalar<int>(nullQuery, commandTimeout: 99999),
    connection.ExecuteScalar<int>(defaultQuery, commandTimeout: 99999),
    connection.ExecuteScalar<int>(notNullOrDefaultQuery, commandTimeout: 99999),
    defaultValue
  );
}

public static string GetColumnTypeString(string type, short maxLength, byte precision, byte scale)
{

  switch (type)
  {
    case "varchar":
      string length = "";
      if (maxLength >= 0)
        length = maxLength.ToString();
      else
        length = "MAX";
      return $"{type.ToUpper()}({length})";

    case "nvarchar":
      string length2 = "";
      if (maxLength >= 0)
        length2 = (maxLength / 2).ToString();
      else
        length2 = "MAX";
      return $"{type.ToUpper()}({length2})";

    case "uniqueidentifier":
    case "ntext":
    case "char":
    case "text":
    case "smallint":
    case "int":
    case "bit":
    case "tinyint":
    case "bigint":
    case "datetime":
      return type.ToUpper();

    case "decimal":
      return $"{type.ToUpper()}({precision}, {scale})";

    case "datetime2":
      return $"{type.ToUpper()}({scale})";

    case "float":
      return $"{type.ToUpper()}({precision})";

  }
  return "";

}

public class Counts
{
  public int NullCount { get; set; }
  public int DefaultCount { get; set; }
  public int NotNullOrDefaultCount { get; set; }
  public string DefaultValue { get; set; }

  
  public Counts(
    int nullCount,
    int defaultCount,
    int notNullOrDefaultCount,
    string defaultValue)
  {
    NullCount = nullCount;    
    DefaultCount = defaultCount;
    NotNullOrDefaultCount = notNullOrDefaultCount;
    DefaultValue = defaultValue;
  }
}

Pre-Build event command line.bat

@ECHO OFF
SETLOCAL ENABLEEXTENSIONS ENABLEDELAYEDEXPANSION
echo --Generated By Pre-build event command line tool > $(ProjectDir)Migrations\Script.PreDeployment.sql
for %%f in ($(ProjectDir)Migrations\Pre\*.sql) do (

    set i=0
    FOR /F "tokens=*" %%a IN ('CertUtil -hashfile "%%~ff" MD5') DO (
        set /A i=i+1
        set op[!i!]=%%a
    )
    set scriptfilename=Pre\%%~nxf

    echo DECLARE @migration_status int = dbo.fnMigrationExists^('!scriptfilename!', '!op[2]!'^); >> $(ProjectDir)Migrations\Script.PreDeployment.sql
    echo if^(@migration_status = 0^) >> $(ProjectDir)Migrations\Script.PreDeployment.sql
    echo BEGIN >> $(ProjectDir)Migrations\Script.PreDeployment.sql
        echo    SET XACT_ABORT ON >> $(ProjectDir)Migrations\Script.PreDeployment.sql
        echo    begin transaction >> $(ProjectDir)Migrations\Script.PreDeployment.sql
        echo    PRINT N'Executing: Pre-Deployment Migration !scriptfilename!'; >> $(ProjectDir)Migrations\Script.PreDeployment.sql
        echo    :r ".\!scriptfilename!" >> $(ProjectDir)Migrations\Script.PreDeployment.sql
        echo    INSERT INTO [dbo].[Migrations] ^([Filename], [Hash]^) VALUES ^('!scriptfilename!', '!op[2]!'^) >> $(ProjectDir)Migrations\Script.PreDeployment.sql
        echo    commit transaction >> $(ProjectDir)Migrations\Script.PreDeployment.sql
        echo    PRINT N'Success: Pre-Deployment Migration !scriptfilename! applied'; >> $(ProjectDir)Migrations\Script.PreDeployment.sql
    echo END >> $(ProjectDir)Migrations\Script.PreDeployment.sql

    echo if^(@migration_status = 1^) >> $(ProjectDir)Migrations\Script.PreDeployment.sql
    echo BEGIN >> $(ProjectDir)Migrations\Script.PreDeployment.sql
        echo    PRINT N'Skipped: Pre-Deployment Migration !scriptfilename! - already applied' >> $(ProjectDir)Migrations\Script.PreDeployment.sql
    echo END >> $(ProjectDir)Migrations\Script.PreDeployment.sql

    echo if^(@migration_status = 2^) >> $(ProjectDir)Migrations\Script.PreDeployment.sql
    echo BEGIN >> $(ProjectDir)Migrations\Script.PreDeployment.sql
        echo    PRINT N'Error: Pre-Deployment Migration !scriptfilename! hash mismatch!' >> $(ProjectDir)Migrations\Script.PreDeployment.sql
        echo    RAISERROR^(50001, 10, 127^) >> $(ProjectDir)Migrations\Script.PreDeployment.sql
    echo END >> $(ProjectDir)Migrations\Script.PreDeployment.sql
    echo GO >> $(ProjectDir)Migrations\Script.PreDeployment.sql
)

echo --Generated By Post-build event command line tool > $(ProjectDir)Migrations\Script.PostDeployment.sql
for %%f in ($(ProjectDir)Migrations\Post\*.sql) do (

    set i=0
    FOR /F "tokens=*" %%a IN ('CertUtil -hashfile "%%~ff" MD5') DO (
        set /A i=i+1
        set op[!i!]=%%a
    )
    set scriptfilename=Post\%%~nxf

    echo DECLARE @migration_status int = dbo.fnMigrationExists^('!scriptfilename!', '!op[2]!'^); >> $(ProjectDir)Migrations\Script.PostDeployment.sql
    echo if^(@migration_status = 0^) >> $(ProjectDir)Migrations\Script.PostDeployment.sql
    echo BEGIN >> $(ProjectDir)Migrations\Script.PostDeployment.sql
        echo    SET XACT_ABORT ON >> $(ProjectDir)Migrations\Script.PostDeployment.sql
        echo    begin transaction >> $(ProjectDir)Migrations\Script.PostDeployment.sql
        echo    PRINT N'Executing: Post-Deployment Migration !scriptfilename!'; >> $(ProjectDir)Migrations\Script.PostDeployment.sql
        echo    :r ".\!scriptfilename!" >> $(ProjectDir)Migrations\Script.PostDeployment.sql
        echo    INSERT INTO [dbo].[Migrations] ^([Filename], [Hash]^) VALUES ^('!scriptfilename!', '!op[2]!'^) >> $(ProjectDir)Migrations\Script.PostDeployment.sql
        echo    commit transaction >> $(ProjectDir)Migrations\Script.PostDeployment.sql
        echo    PRINT N'Success: Post-Deployment Migration !scriptfilename! applied'; >> $(ProjectDir)Migrations\Script.PostDeployment.sql
    echo END >> $(ProjectDir)Migrations\Script.PostDeployment.sql

    echo if^(@migration_status = 1^) >> $(ProjectDir)Migrations\Script.PostDeployment.sql
    echo BEGIN >> $(ProjectDir)Migrations\Script.PostDeployment.sql
        echo    PRINT N'Skipped: Post-Deployment Migration !scriptfilename! - already applied' >> $(ProjectDir)Migrations\Script.PostDeployment.sql
    echo END >> $(ProjectDir)Migrations\Script.PostDeployment.sql

    echo if^(@migration_status = 2^) >> $(ProjectDir)Migrations\Script.PostDeployment.sql
    echo BEGIN >> $(ProjectDir)Migrations\Script.PostDeployment.sql
        echo    PRINT N'Error: Post-Deployment Migration !scriptfilename! hash mismatch!' >> $(ProjectDir)Migrations\Script.PostDeployment.sql
        echo    RAISERROR^(50001, 10, 127^) >> $(ProjectDir)Migrations\Script.PostDeployment.sql
    echo END >> $(ProjectDir)Migrations\Script.PostDeployment.sql
    echo GO >> $(ProjectDir)Migrations\Script.PostDeployment.sql
)