Helper to reset some of the DB structure to EF Core defaults

Rename_Indices_ForeignKeyConstraints_ToEFCoreDefaults_CreateMissingDefaultIndices.sql

create or alter procedure [dbo].[Rename_Indices_ForeignKeyConstraints_ToEFCoreDefaults_CreateMissingDefaultIndices]
as
begin
    set nocount on;
    declare namesCursor cursor for
    select
        names2.schemaName,
        names2.tableName,
        names2.otherTableName,
        names2.columnName,
        names2.currentConstraintName,
        CONCAT('FK_', names2.tableName, '_', names2.otherTableName, '_', names2.columnName) as requiredConstraintName,
        case
            when names2.indexColumnCount = 1 then
                names2.currentIndexName
            end as currentIndexName,
        case
            when names2.currentIndexName is null or names2.indexColumnCount = 1 then
                CONCAT('IX_', names2.tableName, '_', names2.columnName)
            end as requiredIndexName
    from (
        select
            *,
            count(*) over (partition by currentConstraintName) as columnCount,
            count(*) over (partition by currentIndexName) as indexColumnCount
        from (
            select
                schemas.name as schemaName,
                object_name(foreignKeys.parent_object_id) as tableName,
                tables.name as otherTableName,
                col_name(foreignKeyColumns.parent_object_id, foreignKeyColumns.parent_column_id) as columnName,
                foreignKeys.name as currentConstraintName,
                indexes.name as currentIndexName

            from sys.foreign_keys as foreignKeys

            inner join sys.foreign_key_columns as foreignKeyColumns
                on foreignKeys.object_id = foreignKeyColumns.constraint_object_id

            inner join sys.tables as tables
                on tables.object_id = foreignKeys.referenced_object_id

            left join sys.index_columns as indexColumns
                on
                    indexColumns.object_id = foreignKeys.parent_object_id
                    and
                    indexColumns.column_id = foreignKeyColumns.parent_column_id

            left join sys.indexes as indexes
                on
                    indexes.object_id = indexColumns.object_id
                    and
                    indexes.index_id = indexColumns.index_id

            inner join sys.schemas as schemas
                on schemas.schema_id = tables.schema_id

            where
                indexes.name is null
                or (
                    indexes.index_id != 1
                    and
                    indexes.is_primary_key = 0
                )

        ) as names1
    ) as names2
    where names2.columnCount = 1;

    -- https://stackoverflow.com/a/21930372
    declare @transactionCount int;
    set @transactionCount = @@trancount;

    declare @uuid nvarchar(32);
    select @uuid = '6521ccc0773743a88cd1a723213c5747';

    open namesCursor;

    begin try
        if @transactionCount = 0
        begin
            begin transaction;
        end
        else
        begin
            save transaction @uuid;
        end

        declare @schemaName nvarchar(max);
        declare @tableName nvarchar(max);
        declare @otherTableName nvarchar(max);
        declare @columnName nvarchar(max);
        declare @currentConstraintName nvarchar(max);
        declare @requiredConstraintName nvarchar(max);
        declare @currentIndexName nvarchar(max);
        declare @requiredIndexName nvarchar(max);
        fetch next from namesCursor into
            @schemaName,
            @tableName,
            @otherTableName,
            @columnName,
            @currentConstraintName,
            @requiredConstraintName,
            @currentIndexName,
            @requiredIndexName;

        declare @index int;
        set @index = 0;

        declare @tempConstraintName nvarchar(max);
        declare @tempIndexName nvarchar(max);

        while @@FETCH_STATUS = 0
        begin
            if  (
                    @currentConstraintName is not null
                        -- hasn't already changed the name
                        and left(@currentConstraintName, len(@uuid)) != @uuid
                        -- needs changing name
                        and @currentConstraintName != @requiredConstraintName
                )
                or
                (
                    @currentIndexName is not null
                        -- hasn't already changed the name
                        and left(@currentIndexName, len(@uuid)) != @uuid
                        -- needs changing name
                        and @requiredIndexName is not null
                        and @currentIndexName != @requiredIndexName
                )
            begin
                set @index = @index + 1;
                set @tempConstraintName = CONCAT(@uuid, 'constraint', @index);
                set @tempIndexName = CONCAT(@uuid, 'index', @index);

                print 'Schema: [' + @schemaName + ']; Table [' + @tableName + ']; Column [' + @columnName + ']';

                if @currentConstraintName != @requiredConstraintName
                begin
                    print 'Renaming constraint [' + @currentConstraintName + '] to temporary name [' + @tempIndexName + ']';
                    exec sp_rename
                        @objname = @currentConstraintName,
                        @newname = @tempIndexName,
                        @objtype = 'OBJECT';
                end

                if @requiredIndexName is not null
                begin
                    if @currentIndexName is not null and @currentIndexName != @requiredIndexName
                    begin
                        print 'Renaming index [' + @currentIndexName + '] to temporary name [' + @tempIndexName + ']';
                        exec sp_rename
                            @objname = @currentIndexName,
                            @newname = @tempIndexName,
                            -- Don't ask me why it's 'OBJECT' even though the docs say it should be 'INDEX' ...
                            @objtype = 'OBJECT';
                    end
                end

                print '';
            end

            fetch next from namesCursor into
                @schemaName,
                @tableName,
                @otherTableName,
                @columnName,
                @currentConstraintName,
                @requiredConstraintName,
                @currentIndexName,
                @requiredIndexName;
        end

        fetch first from namesCursor into
            @schemaName,
            @tableName,
            @otherTableName,
            @columnName,
            @currentConstraintName,
            @requiredConstraintName,
            @currentIndexName,
            @requiredIndexName;

        set @index = 0;

        while @@FETCH_STATUS = 0
        begin
            if  (
                    @currentConstraintName is not null
                        -- has a changed name
                        and left(@currentConstraintName, len(@uuid)) = @uuid
                )
                or
                (
                    @currentIndexName is not null
                        -- has a changed name
                        and left(@currentIndexName, len(@uuid)) = @uuid
                )
            begin
                set @index = @index + 1;
                set @tempConstraintName = CONCAT(@uuid, 'constraint', @index);
                set @tempIndexName = CONCAT(@uuid, 'index', @index);

                if @currentConstraintName != @requiredConstraintName
                begin
                    print 'Renaming constraint [' + @tempConstraintName + '] to [' + @requiredConstraintName + ']';
                    exec sp_rename
                        @objname = @tempConstraintName,
                        @newname = @requiredConstraintName,
                        @objtype = 'OBJECT';
                end

                if @requiredIndexName is not null
                begin
                    if @currentIndexName is null
                    begin
                        print 'Creating index [' + @requiredIndexName + ']';
                        exec('create index [' + @requiredIndexName + '] on [' + @schemaName + '].[' + @tableName + ']([' + @columnName + '])');
                    end
                    else if @currentIndexName != @requiredIndexName
                    begin
                        print 'Renaming index [' + @tempIndexName + '] to [' + @requiredIndexName + ']';
                        exec sp_rename
                            @objname = @tempIndexName,
                            @newname = @requiredIndexName,
                            @objtype = 'OBJECT';
                    end
                end

                print '';
            end

            fetch next from namesCursor into
                @schemaName,
                @tableName,
                @otherTableName,
                @columnName,
                @currentConstraintName,
                @requiredConstraintName,
                @currentIndexName,
                @requiredIndexName;
        end
        close namesCursor;
        deallocate namesCursor;
        drop table #tempNames;

        if @transactionCount = 0
            commit;
    end try
    begin catch
        close namesCursor;
        deallocate namesCursor;
        drop table #tempNames;

        declare @error int, @message varchar(4000), @xstate int;
        select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
        if @xstate = -1
            rollback;
        if @xstate = 1 and @transactionCount = 0
            rollback;
        if @xstate = 1 and @transactionCount > 0
            rollback transaction @uuid;
        raiserror ('Rename_Indices_ForeignKeyConstraints_ToEFCoreDefaults_CreateMissingDefaultIndices: %d: %s', 16, 1, @error, @message);
    end catch
end