C# helper for SQL Server to change Create to Alter Views and Procedures

MigrationHelpers

using System;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Runtime.CompilerServices;
using System.Text.RegularExpressions;
using Microsoft.EntityFrameworkCore.Migrations;

// This class uses extensions from Nuget package https://www.nuget.org/packages/OLT.Extensions.General

public static class MigrationHelpers
{
    public static string GetSqlTextForDropView(System.Reflection.Assembly assembly, string resourceName, bool useAlter)
    {
        string sql;

        using (Stream stream = assembly.GetEmbeddedResourceStream(resourceName))
        {
            using (StreamReader reader = new StreamReader(stream))
            {
                sql = reader.ReadToEnd();
            }
        }

        if (string.IsNullOrEmpty(sql))
        {
            throw new Exception($"Resource {resourceName} not found in {assembly.FullName}");
        }

        var regPattern = @"^\s*[^(--)]{0}(CREATE +VIEW|ALTER +VIEW)\s+(?<ViewName>(\w|_|\[|\]|\.)*)";
        var regex = new Regex(regPattern, RegexOptions.Multiline | RegexOptions.IgnoreCase);
        var match = regex.Match(sql);

        if (match.Success)
        {
            var procName = match.Value
                .Replace("CREATE", string.Empty)
                .Replace("ALTER", string.Empty)
                .Replace("VIEW", string.Empty)
                .Replace("]", string.Empty)
                .Replace("[", string.Empty)
                .Trim();

            var parts = procName.Split('.').ToList();
            if (parts.Count > 0)
            {
                var name = procName;
                var schema = "dbo";
                if (parts.Count > 1)
                {
                    schema = parts.FirstOrDefault() ?? "dbo";
                    name = parts.LastOrDefault();
                }

                name = name.Replace($"{schema}.", string.Empty);

                return $"DROP VIEW {schema}.{name}";
            }


        }

        throw new Exception("View Resource Not Found");
        //SeedSqlCommandHelper(sql);
    }

    public static string GetSqlTextForCreateView(System.Reflection.Assembly assembly, string resourceName, bool useAlter)
    {
        string sql;

        using (Stream stream = assembly.GetEmbeddedResourceStream(resourceName))
        {
            using (StreamReader reader = new StreamReader(stream))
            {
                sql = reader.ReadToEnd();
            }
        }

        var regPattern = @"^\s*[^(--)]{0}(CREATE +VIEW|ALTER +VIEW)\s+(?<ViewName>(\w|_|\[|\]|\.)*)";
        var regex = new Regex(regPattern, RegexOptions.Multiline | RegexOptions.IgnoreCase);
        var match = regex.Match(sql);

        if (match.Success)
        {
            var procName = match.Value
                .Replace("CREATE", string.Empty)
                .Replace("ALTER", string.Empty)
                .Replace("VIEW", string.Empty)
                .Replace("]", string.Empty)
                .Replace("[", string.Empty)
                .Trim();

            var parts = procName.Split('.').ToList();
            if (parts.Count > 0)
            {
                var name = procName;
                var schema = "dbo";
                if (parts.Count > 1)
                {
                    schema = parts.FirstOrDefault() ?? "dbo";
                    name = parts.LastOrDefault();
                }

                name = name.Replace($"{schema}.", string.Empty);


                var replaceName = useAlter ? match.Value.Replace("CREATE", "ALTER") : match.Value.Replace("ALTER", "CREATE");
                sql = sql.Replace(match.Value, replaceName);
            }


        }

        return sql;
        //SeedSqlCommandHelper(sql);
    }


    public static string GetSqlTextForProc(System.Reflection.Assembly assembly, string resourceName, MigrationBuilder migrationBuilder, bool useAlter)
    {
        string sql;

        using (Stream stream = assembly.GetEmbeddedResourceStream(resourceName))
        {
            using (StreamReader reader = new StreamReader(stream))
            {
                sql = reader.ReadToEnd();
            }
        }

        var regPattern = @"^\s*[^(--)]{0}(CREATE +PROCEDURE|ALTER +PROCEDURE)\s+(?<ProcName>(\w|_|\[|\]|\.)*)";
        var regex = new Regex(regPattern, RegexOptions.Multiline | RegexOptions.IgnoreCase);
        var match = regex.Match(sql);

        if (match.Success)
        {
            var procName = match.Value
                .Replace("CREATE", string.Empty)
                .Replace("ALTER", string.Empty)
                .Replace("PROCEDURE", string.Empty)
                .Replace("]", string.Empty)
                .Replace("[", string.Empty)
                .Trim();

            var parts = procName.Split('.').ToList();
            if (parts.Count > 0)
            {
                var name = procName;
                var schema = "dbo";
                if (parts.Count > 1)
                {
                    schema = parts.FirstOrDefault() ?? "dbo";
                    name = parts.LastOrDefault();
                }

                name = name.Replace($"{schema}.", string.Empty);

                //var exists = $"SELECT COUNT(*) FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_SCHEMA = '{schema}' AND ROUTINE_NAME = '{name}'";
                //var tt = migrationBuilder.Sql(exists);
                //var count = Context.Database.SqlQuery<int>(exists).FirstOrDefault();

                var replaceName = useAlter ? match.Value.Replace("CREATE", "ALTER") : match.Value.Replace("ALTER", "CREATE");
                sql = sql.Replace(match.Value, replaceName);
            }

            
        }

        return sql;
        //SeedSqlCommandHelper(sql);
    }
}